none
Single or multiple DB for different systems? RRS feed

  • Question

  • Hi everyone,

    Im having a hard time deciding what approach should I take. The scenario is this: I have developed various systems (inventory, HR, accounting, etc.). All this systems are (and should be) tightly integrated with one another. At present, for all these systems, i've used a single DB prefixing the tables with the systems name (eg. Inventory.Items).

     

    My question is: did I did the right (and practical) thing? Or should I create a DB for each system to organize them? The problem with multiple DBs is some system uses the other system's table(s). Example, if i created a separate DB for accounting, and a separated DB for inventory, and another for HR, how am I going to relate inventory and HR's accounts to the accounting DB's table? I want a single instance for each table; I don't want to create another account table for inventory or HR so I can enforce integrity. And if different DBs, is there a performance impact on this?

     

    Or is there another way? My concern is performance and manageability. Please help. Thanks!

     

    Friday, June 6, 2008 6:47 AM

Answers

  • Single DB is preferable from the maintenance point of view.  For example, if you are running database mirroring or log shipping for disaster recovery, you need to set it up on database only, not on 5 or 10.  Security is also simpler with the single database solution.

     

    To go multiple DB-s, you must have some good reason to override the reason above. Example, ISP operation where you have to separate customer databases.

     

    In your case, even the integration requirement for the application supports the one db solution.  You don't want to create duplicate tables for accounting.

     

     

     

    Friday, June 6, 2008 6:58 AM

All replies

  • Single DB is preferable from the maintenance point of view.  For example, if you are running database mirroring or log shipping for disaster recovery, you need to set it up on database only, not on 5 or 10.  Security is also simpler with the single database solution.

     

    To go multiple DB-s, you must have some good reason to override the reason above. Example, ISP operation where you have to separate customer databases.

     

    In your case, even the integration requirement for the application supports the one db solution.  You don't want to create duplicate tables for accounting.

     

     

     

    Friday, June 6, 2008 6:58 AM
  • Hi,

     

    Thanks for the reply. I was just wondering if the single database setup is used in real world applications for a very large system since I haven't been involved in any large-scale production DB in the past. At least now I know that the single DB setup for my scenario provides many advantages. Thanks!

    Friday, June 6, 2008 3:41 PM
  • SQL 2005 brings schemas into the picture which allow you to logically group objects in the same database like Oracle does.  The naming schema would be similar to what you have already done, but it expands beyond this to allow for permissions at the schema level, over object level for a bit more simplicity.

     

    Saturday, June 7, 2008 4:58 PM
    Moderator