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.
Lee R Liddick JrReporting AnalystAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
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
 
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:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
chapmandewConnect With a Mentor Commented:
Shoudl be fine...databases are also great for logical/application abstractions...so that might be a good use for it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.