A Developer's Guide to Relationships - Good and Bad

The Key, the Whole Key and Nothing But the Key (So Help Me Codd)

The relational model, invented in 1970 by E F Codd revolutionized computing. It allowed the development of relational databases (such as Oracle, SQL Server and oh so many others) and opened up the data itself to being queried in ways the users of the software systems that went before it could not have imagined. We built hugely on this foundation - massive distributed enterprise systems that span the globe.

We also went a bit mad. What follows are some of the things that I have seen done wrong using (or because of) relational databases.

1. Keeping Potatoes in the Sock Drawer

Database schema changes are discouraged for many reasons - it may be that the database is shared by a number of applications and propagating changes through is too scary, it may be that your developers don't have the database knowledge or it may even be that you have employed a grumpy ogre in the DBA team. What this means is that developers go to extraordinary lengths to make the existing schema match the new requirements - what I call keeping potatoes in the sock drawer.

Do you have a comments field in which special codes need to be entered? Are you using the [User Id] field to hold state information in a multi-step process? Are you storing dates in a VARCHAR field? These are potatoes in the sock drawer and they will give you worry lines.

2. Store v Core v More

The fields in any given database table can be categorized in three groups:

• Store - Fields used to navigate to the record
• Core - Fields used to store facts common to all instances of this record type
• More - Optional fields that may hold

Ideally, you want as much core and as little of the other two as possible. The other way around is all too common though.

For excess "store" fields, this constitutes a waste of resources. For excess "more" fields, there is usually some implicit knowledge required to decide if the emptiness of one of these fields is because the field does not apply to this type of record or if it does but is in fact empty.

3) What is This - A Thing or An Entity?

Many databases have tables that hold records that are totally unrelated to each other - usually for the purposes of lookup of codes. For example, you might have a lookup table that has three fields - category, code and description. To include this table in a query, you need to know and hard-code the category. I've even seen combination tables where there are two or more codes that combine to give a description.

It is more meaningful to split these "lookup" tables by record type - so you can have CurrencyLookup, CountryLookup and so on.

CodeProject