Are there advantages to dividing application databases by usage pattern?

Question

I am working on an application that has two distinct audiences and as such has two distinct types of data. On the one hand there is very high-read/low-write meta data. These tables will have relatively low row counts and will be mostly read by the other side of the application.

The other side of the application is based around very high-write/low-read transactional data. There will be lot of data here and a pretty high velocity on the inserts. This part of the application will read some data from the meta side but won't write anything back to that side.

There won't need to be any RI between these two buckets of tables.

My question is this: Does it make sense to create two separate databases for these very different types of data? What are the advantages and disadvantages to both approaches?

If it matters, this is built in .NET using a SQL Server 2008 back-end.

Answer 1

I would recommend taking time to make sure the design doesn't hinder this kind of separation in the future, but implement it as a single database.

It smells of premature optimization. There are a lot of high-powered database servers that can perform equally well under high load. Even still, there are solutions for replication that can be done as multiple servers with custom data replication, or back-end data accessing the same physical storage via fiber.

I think you will save a lot of potentially needless work by avoiding this early in your project's implementation. But it won't take a lot of time to forsee a method by which it could be accomplished in the future (via class design, 3-tier architecture, or even database stored procedures (icky) ).

Answer 2

Based on your descriptions, it sounds like you have two distinct services (from the perspective of Service-Oriented software.) One of the major tenants of SOA is that your services should be autonomous, but autonomy is very difficult to achieve so long as you have a shared database behind them. You mentioned several times in your description that the data is very different, and that the applications are pretty independant of each other. That is a good indication that you have a clear boundary of separation between what could be considered two distinct services, which should each have their own database to improve autonomy. You could have the high-write/low-read service consume the high-read/low-write service, or, you could have the high-read service periodically publish changes to any subscribers (i.e. the high-write service), each of which would cache the data they need locally in their own database.

A very good description of this type of service-oriented design can be found in this video by Udi Dahan: http://www.vimeo.com/5022174. It might be helpful in helping you solve your problem.

Answer 3

I agree with Kieveli, but would like to add that because of the low row counts for the high-read tables, you could look at caching this data to avoid most hits on the database (except initialisation and refreshing stale caches).

Separating out the data access layer for caching should, with a suitable design, help make it easier to move it to a different database in the future if needed.