Database strategy for connected but disparate applications

Question

I'm working on a series of inter-related but separate web applications, all situated on the same host:

Super Admin
|
---Site Network Admin
|   |
|   ---Site
|   |
|   ---Site
|   |
|   ---Site
|
---Site Network Admin
    |
    ---Site
    |
    ---Site
    |
    ---Site

All three will have access to the same core library (Zend Framework), and separate application libraries for each tier in the network - thus 3 applications and one library - but different configurations to support unique instances.

I'm interested to hear people's opinion on the best database strategy to support these applications.

On the one hand, I could deploy one database, or perhaps one for each application level (i.e. 3). This has the advantage of being easy to roll out changes, but the disadvantage that users share the access burden - i.e. if one user is really hammering the database, the performance of other applications sharing the database will suffer. Another disadvantage is that any data corruption could potentially affect all end users.

On the other, I could utilise a series of databases - one for the first two tiers to share administrative data - but separate instances for each site deployed on the network. This has the advantage that end users are isolated and thus data corruption is more easy to contain. The disadvantage is obviously the converse of the previous example - in that changes are then harder to roll out across the network.

My thinking is that it's dictated by usage vs user population. If you have low quantities of users, but they interact with the database a lot - then plan B is best. If it's the former, that you have high yields of users with low interaction rates - then plan A is best.

I'm interested to hear other people's thoughts as to the best approach for dealing with this situation.

Answer 1

I think you've covered the major arguments for both scenarios very well. There may also be some efficiency around database disk access for fewer databases (fewer and/or shorter seeks), but it shouldn't be dramatic. Having more databases also probably means smaller memory usage for indexes as well - instead of (site_id, post_id) composite keys, you might just have (post_id) keys.

Depending on the likelihood of project success (ie, startup vs. internal company project), I would generally suggest going with a hybrid approach.

At first, go with a database design that allows you to store multiple instances of your application within the same database. This is easier in the short term in terms of administration and maintenance - you don't need to repeat manual or automatic actions over a number of databases. This allows you to focus on being successful in developing your application for your initial sites.

If you end up being successful and need more database capacity, then you can change to having multiple database servers - each database server handles multiple sites. In the early initialisation stage of your application, you choose which database server to use based on the site.

If you go with the "each instance has its own dedicated database" approach, you will initially spend a lot of time solving the multiple database problem while you could be spending it increasing the likelihood that your project is successful.

Either way, make sure you have database backup and restore covered and well-understood.