SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
Thread: Many tables or many DataBase?
-
Jan 27, 2006, 15:12 #1
- Join Date
- Nov 2005
- Posts
- 92
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Many tables or many DataBase?
Hi,
I would like to know if it's better in terms of performance to have many table in 1 database or if it's better to have many DataBase with few tables....
Thanks
-
Jan 27, 2006, 15:57 #2
- Join Date
- Jul 2004
- Location
- Nigeria
- Posts
- 1,737
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
1 database. Many tables
-
Jan 28, 2006, 13:22 #3
- Join Date
- Jan 2006
- Location
- America
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by afrika
-
Jan 28, 2006, 13:27 #4
I only have 3 databases so , few databases many tables.
-
Jan 28, 2006, 14:15 #5
- Join Date
- Jul 2004
- Location
- Nigeria
- Posts
- 1,737
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
If you have a good naming convention for your objects, one database gives you much more flexibility, with centralized accounting, reconcilliation , security etc
...and many other issues
-
Jan 28, 2006, 16:54 #6
- Join Date
- Aug 2003
- Location
- CT
- Posts
- 643
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I guess it depends on what you are doing.
For a typical website, such as sitepoint, i see no need for more than 1 database.
tables named like:
sitepoint_articles_xxxxxxx
sitepoint_forums_xxxxxxx
and so on, easily seperate it out, and make it easy for making the whole site dynamic.
In an enterprise situation though, It might be best to split it out into seperate DB's
At my office, we generally set up 1 database per client. But, these aren't web sites. these are databases of client info, products, or whatever, that would NEVER be joined to another company.
-
Jan 28, 2006, 17:13 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by builder
the question about performance has no meaning when you consider only the containers
performance of what?
if the question is, how to return data from one table, it makes no difference whether you have many tables in one database or many databases with few tables
so the real question is, what do you want to do?
if you want to return data from all tables, that's a differnt question, isn't it?
-
Jan 28, 2006, 17:23 #8
- Join Date
- Jul 2004
- Location
- Nigeria
- Posts
- 1,737
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
>>so the real question is, what do you want to do?
Yes Sir
I used to have that mentality, when i first to into dataware housing, thinking that due to the heavy load, its best to spread it accross databases.
ADVICE: Get a good naming convention for your objects
accounting_Financials_PO
users_Admin_users
users_login_history
etc, a hierachy of objects, clearly defined
-
Jan 30, 2006, 12:08 #9
- Join Date
- Aug 2004
- Posts
- 428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hierachy of objects
hierachy of objects is important.
however i limit the use of underscore as usually thats notation for foreign keys or many to many relationships
example:
2 tables: user, permission
to join with many to many i use : user_permission
I also try to show which tables are related together by:
Users, UsersPermission
Users_UsersPermission
----------------
I try to use the pascal notation: PascalNotation for table names
for column names I use the camelCase
Bookmarks