Click here to Skip to main content
Click here to Skip to main content

A developers guide to relationships - good and bad

, 24 Mar 2014
Rate this:
Please Sign up or sign in to vote.
The key, the whole key and nothing but the key (so help me Codd)The relational model, invented in 1970 by E F Codd revolutionised 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 t

The key, the whole key and nothing but the key (so help me Codd)

The relational model, invented in 1970 by E F Codd revolutionised 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 categorised 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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Duncan Edwards Jones
Software Developer (Senior)
Ireland Ireland
C# / SQL Server developer
Microsoft MVP 2006, 2007
Visual Basic .NET
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionIt gets worse PinpremiumDuncan Edwards Jones28-Mar-14 1:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 25 Mar 2014
Article Copyright 2014 by Duncan Edwards Jones
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid