Click here to Skip to main content
13,044,398 members (120,549 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 24 Mar 2014

A Developer's Guide to Relationships - Good and Bad

, 24 Mar 2014
Rate this:
Please Sign up or sign in to vote.
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.


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 (Azure) 2017
Microsoft MVP (Visual Basic) 2006, 2007

You may also be interested in...

Comments and Discussions

QuestionIt gets worse Pin
Duncan Edwards Jones28-Mar-14 1:43
professionalDuncan Edwards Jones28-Mar-14 1:43 

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

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

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