Don't Destroy the Past

Brian Coyner has an entry on the O'Reilly weblogs about bad database design leading to even worse code.

First, let me say: Here, Here! and Huzzah!

When you design an application, you must spend time over-designing your database before you write any code. Assume that every requirement that you have been given will change before the bits are dry in your inbox. Assume that every time your business analyst or user liason or boss said "That's as far as we will ever go with this product", they're wrong (or lying, your choice). Modifying code is a thousand times faster, easier, more reliable, and cheaper than a database modification and conversion. Studies have indicated that maintenance consumes up to 85% of the software budget. My experience has been closer to 98%. If you can move effort from maintenance to development, you move expenses from the cost center to the profit center.

Brian has a little example in this article, though, which I just have to jump on. He promotes the use of non-mutable key values for tables. That is, the key should not contain any business information which could change. I agree 100%. The example shows that by moving an account number from a key column to a non-key column, only an update of the non-key column is required.

What is a column update? It is the destruction and loss of data by replacing it with new data. What happens when someone (auditing, marketing, business intelligence applications) go looking at this customer or vendor? They see a history of events (payments, orders, disciplinary actions) against the current account number. It may be that none of those events pertain to that account. They occured when the value in that column was something different.

So when you sit down to overdesign the database for your next application, think about how you will preserve and track all the daily little changes to your application's datastore. That store is not simply a snapshot of the current state. It must represent the changes that have occured and the timeframes in which they occurred.