Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Possible Duplicate:
Should I use one database per application or share a single database amongst multiple applications

We have a bunch of small to medium sized apps, each of which has its own database (MSSQL Server). There was a suggestion that we consoldate the 'related' databases into a smaller set amount of larger databases.

They don't particularly share a lot of data, they would just be under a similar business group. For example, using a 'Finance' database to hold the tables and procedures for finance apps.

Would it be appropriate to use a different schema for each app, e.g.

App1.SomeTable
App1.SomeOtherTable
AppTwo.SomeTable

What are the pros and cons of this approach? What should I watch out for?

share|improve this question

marked as duplicate by FrustratedWithFormsDesigner, gnat, maple_shaft Jun 21 '12 at 3:41

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
Yeah I saw that one. That question seemed to be dependent on the fact that the applications/databases would share a lot of the same data. Since there is not a lot of sharing between ours, I wasn't sure if the same issues applied. I am wondering if there is an upside or downside to consolidating, or if this is a case of 'If it ain't broke, don't fix it' –  King Jun 20 '12 at 17:56
    
There have been similar questions before, that was the first one I could find (also probably on dba.stackexchange.com/questions) The issue of rolling out structure changes to many instances will still be a problem, even if you don't have to worry about shared reference data. –  FrustratedWithFormsDesigner Jun 20 '12 at 17:57

3 Answers 3

up vote 5 down vote accepted

At the risk of stating the obvious, in general, if you share the DBs, you're coupling the applications and making DB changes more "global." In most situations, that's probably a bad idea, for the same reasons that broadly-scoped variables are generally a bad idea. However, if there is significant effort in maintaining or optimizing the DB, then you might have some gains (e.g., if your DBA tunes up the DB to perform better, everyone gets the boost, or if you spend a significant amount of effort auditing and cleaning the data).

share|improve this answer

This drastic of a change should be done to solve a problem. There are pros and cons of multiple/more vs single/fewer databases. It depends on the problems you're trying to solve.

Do your developers struggle trying to find where the data are? It may be easier to work in the Finance app knowing there is a single Finance db.

Is referencing two tables in a query from different databases really that hard?

Backups - restoring at the database level is pretty easy. The fewer tables and other parts the easier it gets. Yes, you can break up large databases into multiple files. It's only going to make it harder to maintain a development database if you have to transfer massive files especially when you only need a specific section.

More Servers - it's easier to move a database to another server. It's much harder to put one database on several pieces of hardware.

I don't know what your situation is, but there just doesn't seem to be any justification for one database. If problems come up, reconsider.

share|improve this answer

Integrating the source of information is a good thing to seek. In fact, organizations spend millions to integrate their disparate decision support systems and operational systems. One needs to determine:

1-What are the shared data?

2-How often does this data change?

3-How large is the volume of this data?

4-Can the data be re-entered manually or is that too time consuming and/or error-prone?

5-How sensitive is the shared data?

6-What is the cost of automation?

In some cases, specially with small business, the number of transactions is not that large and it would probably take some one less than 1 hour to enter the information manually. The cost of this effort can be measured in dollars against the proposed automation cost.

You may want to use a matrix similar to the crude example below (I know it is neither accurate nor complete!) to show the data shared and how this data is shared. The left hand side represents the table and horizontal column headers represent the different systems. There is an analysis technique called Clustering Analysis that could be used to show good system boundaries, but I don't have an electronic reference for it (it is old!).

enter image description here

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.