Many tables or many DataBase?

[builder]

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

[afrika]

1 database. Many tables

[Rustic]

1 database. Many tables

I second that. Create 1 database with a lot of tables.

[Aaroniza]

I only have 3 databases so , few databases many tables.

[afrika]

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

[briansol]

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.

[r937]

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....

neither

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?

[afrika]

>>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

[leblanc]

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