[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

One database versus multiple databases

I have a website that contains multiple different web based applications.  Some of the applications contain quite alot of data and is used nationally on a daily basis.  

I'm wanting to know is it better to create multiple databases for each of the highly utilized applications and link all the databases together or have one database that contains all the tables for each of the different applications.

So...1 Website, 5 major applications.  Do I have it so I have DATABASE01 with every table from the 5 major applications along with the rest of the tables needed for the site?  Or do I have DATABASE01 with all the tables needed for the site and the minor/small applications, then create DATABASE02 for Application1, create DATABASE03 for Application2, create DATABASE03 for Application3, create DATABASE04 for Application4, and create DATABASE05 for Application5...then link the separate databases to each other so they could be queried together, if needed.
0
Lee R Liddick Jr
Asked:
Lee R Liddick Jr
  • 3
  • 3
  • 2
2 Solutions
 
chapmandewCommented:
It depends....multiple databases means you have mulitple user accounts you must administer.  One means that you administer one...you can still segment your data among different drivers through filegroups...but it takes planning!
0
 
pcelbaCommented:
If your data are strictly separated for each application then it is better and safer to have more databases. If you need to query data from different databases in each application then one database is a good choice. You may also distinguish data by different schemes in one database.

The data size and traffic is also important. If you know about an application which will grow rapidly or which could load the server resources significantly more than other applications then you should use separate database for this specific application.

The administration cost as mentioned by chapmandew is also important, of course... So, it depends.
0
 
pcelbaCommented:
A small clarification:

The sentence: "If you need to query data from different databases in each application then one database is a good choice." should rather be "If most of your data are common for several applications then one database is a good choice." Applications which are using common data are not different apps but One application only.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Lee R Liddick JrReporting AnalystAuthor Commented:
There are a handfull of tables from DATABASE01 (like a login profile table) that would need to be queried with all the other databases.  So basically what you are saying is that the queries run over linked databases will not process as quickly as those from the same database.  
What I am trying to avoid is a space issue on using just one database for some major tools/applications that are being used on the website.  That is why I am thinking I would need multiple databases to separate the data out.
0
 
chapmandewCommented:
databases on the same server instance are fine...across linked servers is going to be slower.

0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
All of the databases would be on the same server...each of the other databases would be linked to DATABASE01 to query things like employee table, location table...things of that nature.
0
 
pcelbaCommented:
Agree.
0
 
chapmandewCommented:
Shoudl be fine...databases are also great for logical/application abstractions...so that might be a good use for it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now