Thursday, January 08, 2009

There Can Be Only One…..Database.

Ok, here’s the premise.  You build web apps for a living.  In general, a web app needs a database.  But should it ever have or need more than one?

You’ve got one web app, Primary, that uses PrimaryDB.  PrimaryDB has tables First, Second, … and so on, up to let’s say Tenth.  With me?

You also have a web app, Secondary, using SecondaryDB with tables Blue, Green and Red.  Yes I deliberately switched conventions to make it a little easier to understand.

So, new project comes along, and it turns out that Secondary needs access to tables First through Ninth.

What do you do?  Some options:

* Copy the tables from PrimaryDB to SecondaryDB and maintain two copies.

* If your database supports it, create “synonyms” on Secondary for the Primary tables.

* Configure the Secondary application to enable access to both PrimaryDB and SecondaryDB.

* Split First through Ninth into a new database, CommonDB, and put CommonService in front of it.  Tell Primary and Secondary that this is the new way to access that resource.  (In a pinch this can also be simulated by putting a special page/interface inside of Primary that exposes just those parts of PrimaryDB to be shared, and let Secondary hit that as a web service.  Not optimal, as  you can’t independently scale that service based on need, but if you had limited operational resources for bringing up new production servers you could go that way.)


Thoughts?  At one time or another I’ve done all of the above. I’m a fan of the last one, if you can reasonably assume that this is not the last time you’ll be asked to do this.  Maybe instead of nine tables it’s 90 tables, and maybe instead of just Secondary needing access to the data, you can envision Tertiary and Quarternary and a whole bunch more apps to come, all of which are going to need access to those tables as well?

[Since technology matters, I’ll point out that the database in question here is MySQL. The actual technology for the webapps now is PHP, although for the solution we’d be open to Rails or .Net as well.]

No comments: