Single DB vs. Multiple DBs

Results 1 to 4 of 4

Thread: Single DB vs. Multiple DBs

  1. #1
    Grant Young Guest

    Default Single DB vs. Multiple DBs

    Hi all. Hope this hasn&#039;t already been answered. Had a look around but couldn&#039;t find it, so here goes...<BR><BR>Under what circumstances would you use multiple databases versus one big DB?<BR><BR>I have a project where most the functionality is scoped to a specific organisation. There is a potential that the number of entries in the main database will become large - perhaps even as high as 100,000 new entries per month (50 per organisation w/ 2000 organisations).<BR><BR>My question is: should I build the system so that each organisation has its own database/set of tables, or should I continue down the (current) path of storing everything in the one DB and simply referencing the organisation in each?<BR><BR>We are still in early prototype stage, so the DBMS has not yet been decided (assuming SQL Server or equivilent in the final version).<BR><BR>TIA for any assistance.

  2. #2
    Join Date
    Dec 1969

    Default Well...

    ...SQL Server should be able to handle all of the "organizations"<BR>(hint hint) just fine. With that much data, you&#039;ll want to look at any archive tables that could contain not used very often and adjust your code to give an option to retrieve from old archives or not.<BR><BR>Your bigger issue with that much data will be CPU and memory.<BR>The database can handle it just fine. Cross database queries are certainly possible but are definitely slower...

  3. #3
    Jeremy_D Guest

    Default RE: Well...

    Speed difference between in-database and cross-database queries is very small. I would say that the main reason to seperate the different organizations over multiple DBs would be your backup and restore strategies. If everything is in the same DB and you need to restore a table you&#039;ll have to restore the complete DB, which would obviously take more time then restoring only the DB for the relevant organization.

  4. #4
    Grant Young Guest

    Default RE: Well...

    Thanks.<BR><BR>A follow-on question:<BR>Can anyone recommend a good resource (online, book, whatever) that can assist in determining hardware/storage requirements for a db system. For example, a resource that allows you to estimate required h/ware & storage for X hits, or X records etc.<BR><BR>TIA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts