Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adam2k1
Starting Member
6 Posts |
 Posted - 2009-06-21 : 13:26:51
|
| I've been developing database driven applications and web sites for a while now, but have a fairly simple question.In my previous job we broke everything down into separate databases, for example: one for all customer details (names, addresses, users etc), one would be for orders (products, invoices etc) and so on.In my current job we lump everything into one database and its made me wonder what the best strategy is. Having separate database seems more logical to group common tables together, and if providing to a new customer you only give them access to the databases containing the tables they require.On the other hand, it means you often cant enforce foreign constraints etc as fields exists in another database. That to me in itself would suggest the tables should be in the same database...I was wondering what your take on it was? |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2009-06-21 : 17:45:51
|
| Youll find that the answer to this, like most data modeling questions, is "it depends." I try to keep domains of data that are transactionally dependent in a single database to simplify the enforcement of RI (as you mentioned). A great way to get the best of both worlds is to use the schema object. This allows you to divide your db into domains of interest, or "group common tables together" as you put it. For example, creating schemas for HumanResources or Sales allows you to apply security at the schema level. It also sorts the objects in Mgmt Studio which is nice. |
 |
|
|
adam2k1
Starting Member
6 Posts |
Posted - 2009-06-22 : 04:19:54
|
| Thanks nathans.As you said, it will depend on the application itself, but this is exactly what I was after :)Cheers,Adam |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-22 : 04:24:50
|
One reason before to use multiple databases could have been that partitioning was not available before.Although there were multiple filegroups, that option was not used widely.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
DB Design: One V Multiple Databases