Bad DB Design Leads To Horrible Code

by Brian M. Coyner

Why are some people clueless about database design? I have seen time and time again database designs that are completely wrong, especially when assigning primary key columns.

I have seen database models where the primary key is a mutable field. Most of us know, or we should know, that once a primary key is set that key should NEVER change. For some reason, some people do not see a big deal with this strategy. They say, "Well just erase the old record and insert a copy with the new key value." This is wrong. If you know that a field is mutable then do not make that field a primary key. Instead use an auto-generated number (if your database supports it, which most should), or come up with some other bogus key.

But you may say, "Well the legacy database forces this design." My response, "No it does not!". Typically the only time a legacy application matters is during the data conversion process, which is a one-time deal. Spending a little extra effort on the conversion scripts can ensure that the new model is clean and maintainable.

Why does it matter... I am just a programmer? A clean database design is critical for software development. The cleaner the database design, the easier your server side logic is to implement, and the faster your server side code executes.

Here's an example:

You need to change the value of an account number on the account table. Here's a possible solution if the account number is the primary key:

  1. Retrieve the original data from the database (store this data in memory)

  2. Delete the original data from the database

  3. Update the data in memory with the new account number

  4. Add the new account record to the database

NOTE: this takes a minimum of three database calls.

Here's how to do this if the account number is not the primary key:

  1. Update the account number on the account table

NOTE: this takes one database call and hardly any code.

I don't think it takes a genius to design good database models. A little common sense and a big white board leads to a clean and agile database model, which in turn, leads to a clean and agile code base.