An example of why DBAs make money

October 26, 2005

(The following example is a simplified and modified version of an online discussion I had recently. All errors are my fault.)

Here's an example large database problem; it's simplest to cast this as a customer support system that you sell to big companies.

In your first version, the customer support application database system includes a table of companies that are being supported.

Multiple groups write various programs against this database, and in part against this table and other tables hooked to it (such as people who can make support calls). Perhaps the people you sell the system to even write their own tools that integrate with the database.

In a later version, you add the feature of having 'evaluation' support, for companies that are just testing the waters and not yet paying your customers for full support. Some support things only want to work for paying companies; some should work with all companies.

How do you change the database to do this?

The clean and morally correct solution is to either add a 'paying?' field to the company table, or to add a new 'company status' table, keyed to the company ID. Unfortunately, either option means that non-paying companies will appear in the 'company' table. Any code that assumes that the 'company' table only contains fully paid up and privileged companies is now wrong and possibly a ticking time bomb.

You could try replacing the 'company' table with a view that excludes evaluating companies, but this may perform badly or not work at all. (There are at least a lot of beartraps in the foothills of this.)

If you change the database, anyone upgrading has to have a flag day where all applications may have to be upgraded in sync with the database. This can be very difficult to schedule (especially in large companies with multiple development groups), and it's a major change; companies don't like those. Even if you have a high-level API for accessing your database in a library, people upgrading still need a flag day where all applications get relinked against your new libraries that understand your database change.

What you really need is an in-database API: in other words, everyone uses stored procedures and never uses raw SQL. (Alternately, you need everyone to always go through a higher-level service that runs on the database machines, but this may have performance problems.)

What you may wind up with instead is introducing a new 'evaluating companies' table, and then updating a bunch of code to union the two tables (or use a view that does it automatically) if they want to work with both sorts.

Written on 26 October 2005.
« A smooth disk swap
Databases are APIs »

Page tools: View Source, Add Comment.
Login: Password:
Atom Syndication: Recent Comments.

Last modified: Wed Oct 26 01:51:33 2005
This dinky wiki is brought to you by the Insane Hackers Guild, Python sub-branch.