Agile Database Design

This page is about database design, but please don't scroll away yet! - it is not so complicated; you should do it incrementally to blend in with your agile project; you need to know a few basic rules that will save you grief later; and you need to know that you can always change your database design. If you find yourself in an organisation that has a team of data designers which is an elite secret society, change it now if you have the power, or find a better job if you don't.

We're going to talk about identifiers and links in databases, as well as some simple habits to keep our databases clean and efficient. We are going to find out the important bits and absorb them into our day-to-day work. We will mention the terminology, just so that when people try to blind us with mathematics, we'll know what they are talking about and be able to distinguish useful stuff from jargon which is used to preserve a false aura of superiority. With a bit of luck, we'll never get bamboozled by database terminology again.

Who Is In Charge?

You are! I have often heard people say, "I don't want the database telling me what to do". This is back-to-front. Maybe you are the victim of an over-reaching Database Administration department. If so, you have my sympathy - see chapter ? - Database Administration.

The point is that we tell the database what we would like it to do. There are many useful things it can do for us. If we want the database to hold a date in a particular field, then the database will make sure that this field always contains a valid date. If a particular field can be missing sometimes, we can tell the database that this is OK and it will not complain. If a particular field is the link ("foreign key") to a record in another table, the database will make sure that the record being referenced exists and that it is never deleted while we are still referring to it.

There are many cases where we want the database to ensure the quality of our data and this is very helpful. After all, if we don't care how accurate our data is, why are we bothering to store and manage it?

Identifiers

Identifiers, or keys, are used to let us access the specific record that we are interested in. In the early days of databases, people used labels that already existed, so the record of an individual customer would be identified by their name. As customer systems grew from hundreds of people to hundred of millions, it quickly became obvious that names would not do as identifiers. Most of us have come across two people with the same name in our school, or work. My name is not particularly common, but it has popped up in in some places where I wasn't expecting it, even a television show.

So then people started adding other bits to the key - we'll see some examples below.

The simple solution is to give every record a number. These numbers may or may not be used outside the computer system, and we should store the person's name as well (if our computer system is going to communicate with that person). Using a unique whole number is just the simplest way of not getting people and their stuff mixed up. If one of these people is you and your stuff includes your bank account or your health records, then you do not want it to get mixed up in any way, especially not with someone else's data.

These unique record numbers are called "surrogate keys" because they stand in for the various combinations of attributes that might be needed to precisely identify one person or one thing. Surrogate keys are a way to:

simplify our database designs
improve our database performance
comply more easily with all the higher normal forms.

("higher normal forms" are part of the jargon. You need to know what they are and you need to apply the relevant bits to your database designs, but we can learn and describe them without getting tangled up in the jargon. Keep reading to find out how.)

An Example

Example of Surrogate and Natural Keys - Database Design

This diagram shows a customer ⇨ account ⇨ transaction hierarchy using natural keys and surrogate keys. It describes a situation in which a customer can have several accounts and an account can have many transactions.

Data items (or "fields") with a blue key () next to them are parts of a "primary key" - the identifier of the record.

Data items with an orange key () next to them are parts of a "foreign key". A foreign key links to a record in another table; it is the value of the primary key in the record it is linking to.

Natural Keys

In the natural-key model, keys serve as identifiers and frequently also hold some attributes of the record. These attributes may appear in full, but more often are coded. The details in the example, explained below, illustrate this.

Natural keys are often made up of two or more fields. A key which has more than one field is called a "compound key". Compound keys can lead to all sorts of confusion. We explain, below, how surrogate keys eliminate these problems.

Surrogate Keys

A surrogate key is a single field containing a number which is the identifier (frequently abbreviated to "id") of the record. Every record in a table has a different id (obviously).

Here is a list of the characteristics we want for surrogate keys.

In any particular table:

a surrogate key is simply a unique identifier; it contains no other information.
a particular surrogate key value is forever; for a particular record in a particular table, it never changes.
a particular surrogate key value is never re-used in the same table, even if the record it identifies is deleted.
the order of the surrogate keys is irrelevant; they cannot be used to sort records into any meaningful order.
when a new record is added, it is given a surrogate key that has never been used before in this table and will never be used for another record in this table.
although the same number may be used for surrogate primary keys in different tables, the number used in one table has no relationship to the same number used as a primary key in another table.
a surrogate key used as a foreign key in one table matches the primary key of the referenced table - it has the same value. This is how we safely link records together, with help from the database.

Primary and Foreign Keys

A primary key is the identifier of a record. In the Natural Key model, the identifier of each record in the account table is the combination of the customer_key and the account_key, we have to know both to choose the correct account. In the Surrogate Key model the id in the account table is the primary key.

Data Using Natural Keys

Here is some sample data in the natural key model

The customer table

          customer_key   | first_name |  last_name  
        -----------------+------------+-------------
         HOWBR/44325/A05 | Jerome     | Howbridge
         ROONE/76467/B07 | Rapha      | Rooney
         WHITE/90801/A05 | Nigel      | White
         MAY--/68504/A04 | Bradley    | May
         ADNAM/88303/A03 | Deborah    | Adnams
         PARRO/72742/C04 | Thurstan   | Parrott
         POLIN/48519/A04 | Patrick    | Poline
         BLAIN/31849/B01 | Somer      | Blaine
         LASHA/47533/C06 | Marina     | Lasham
         RAVEN/77318/B07 | Aulay      | Ravenscroft
         EADE-/91973/B06 | Zandra     | Eade
         RANSL/97057/C01 | Miles      | Ransley
         THEZE/80711/B11 | Callum     | Theze
         ARBER/47447/D04 | Conlan     | Arberry

The account table

          customer_key   | account_key | account_type | date_opened 
        -----------------+-------------+--------------+-------------
         HOWBR/44325/A05 | C102764     | Current      | 2013-05-23
         HOWBR/44325/A05 | S100234     | Savings      | 2016-03-31
         HOWBR/44325/A05 | I534278     | Investment   | 2018-07-06
         ADNAM/88303/A03 | C635273     | Current      | 1996-02-15
         ADNAM/88303/A03 | L777253     | Loan         | 2005-09-23
         LASHA/47533/C06 | C536352     | Current      | 2017-01-13
         RANSL/97057/C01 | C644528     | Current      | 2009-11-22
         RANSL/97057/C01 | S155281     | Savings      | 2010-10-07

The transaction table

          customer_key   | account_key | transaction_key | created_date |  amount  
        -----------------+-------------+-----------------+--------------+----------
         HOWBR/44325/A05 | C102764     |               1 | 2023-05-23   |    15.27
         HOWBR/44325/A05 | C102764     |               2 | 2023-05-23   |  -420.72
         HOWBR/44325/A05 | C102764     |               3 | 2023-05-24   |    24.67
         HOWBR/44325/A05 | C102764     |               4 | 2023-05-26   |   113.50
         HOWBR/44325/A05 | C102764     |               5 | 2023-05-27   |     4.52
         HOWBR/44325/A05 | S100234     |               1 | 2016-03-31   |  1400.00
         HOWBR/44325/A05 | I534278     |               1 | 2016-03-31   | -1000.00
         ADNAM/88303/A03 | C635273     |               3 | 2005-03-15   |  -500.00
         ADNAM/88303/A03 | C635273     |               4 | 2005-04-15   |  -500.00
         ADNAM/88303/A03 | C635273     |               5 | 2005-05-15   |  -500.00
         ADNAM/88303/A03 | C635273     |               6 | 2005-06-14   |  -500.00
         ADNAM/88303/A03 | C635273     |               7 | 2005-07-15   |  -500.00
         ADNAM/88303/A03 | C635273     |               8 | 2005-09-25   | -2500.00
         ADNAM/88303/A03 | C635273     |               9 | 2005-09-25   |  4750.00
         ADNAM/88303/A03 | L777253     |              10 | 2005-09-23   |  2500.00

In this example of the Natural Key model, for the customer table, the customer-key holds the first 5 letters of the surname, a 5-digit number and a code consisting of a letter and two digits. Keys in this style are quite common in older systems. We would not design a new system like this. This design has many flaws but we have seem them all in the real world and we can imagine what the key-designer's thought process might have been - maybe something like:

let's start with the surname - that is the first thing we would ask the customer for, so that we can start to identify her

but we have several people with the same surname so we'll add a number, making sure it is unique for each of the separate people with the same name

then it would be useful to know what type of customer they are. We don't have many customer types, so a single letter is enough

and we also would like to record how many years they have been with us, so we'll add a 2-digit number for that.

The account key is simply a letter (possibly the account type) and a six-digit integer.

The transaction key looks like a serial number, starting at 1 for each customer account.

It is silly to store the number of years the customer has been with us, since this will change every year. If we care about this information we should store the year that the person became a customer.

There are many things wrong with this approach to key design, but it is very common. (This example was inspired by the identifiers on my current car insurance policy.) We will go through the problems illustrated here, and others that occur, when we get further into design.

When we migrate from a design that uses natural keys, as in the table customer above, to a design that uses surrogate keys, any information embedded in the natural key is recorded in separate attributes, if it is useful.

Data Using Surrogate Keys

The customer table

         id | first_name |  last_name  | customer_type 
        ----+------------+-------------+---------------
          1 | Jerome     | Howbridge   | Individual
          2 | Rapha      | Rooney      | Joint
          3 | Nigel      | White       | Individual
          4 | Bradley    | May         | Individual
          7 | Deborah    | Adnams      | Individual
          8 | Thurstan   | Parrott     | Business
          9 | Patrick    | Poline      | Individual
         10 | Somer      | Blaine      | Joint
         12 | Marina     | Lasham      | Business
         13 | Aulay      | Ravenscroft | Joint
         14 | Zandra     | Eade        | Joint
         15 | Miles      | Ransley     | Business
         18 | Callum     | Theze       | Joint
         20 | Conlan     | Arberry     | Charity

The account table

 id | customer_id | legacy_account_number | account_type | date_opened 
----+-------------+-----------------------+--------------+-------------
  1 |           1 | C102764               | Current      | 2013-05-23
  2 |           1 | S100234               | Saving       | 2016-03-31
  3 |           1 | I534278               | Investment   | 2018-07-06
  4 |           7 | C635273               | Current      | 1996-02-15
  5 |           7 | L777253               | Loan         | 2005-09-23
  6 |          12 | C536352               | Current      | 2017-01-13
  7 |          15 | C644528               | Current      | 2009-11-22
  8 |          15 | S155281               | Saving       | 2010-10-07

The transaction table

 id | customer_id | account_id | created_date |  amount  
----+-------------+------------+--------------+----------
  1 |           1 |          1 | 2023-05-23   |    15.27
  2 |           1 |          1 | 2023-05-23   |  -420.72
  3 |           1 |          1 | 2023-05-24   |    24.67
  4 |           1 |          1 | 2023-05-26   |   113.50
  5 |           1 |          1 | 2023-05-27   |     4.52
  6 |           1 |          2 | 2016-03-31   |  1400.00
  7 |           1 |          3 | 2016-03-31   | -1000.00
  8 |           7 |          4 | 2005-03-15   |  -500.00
  9 |           7 |          4 | 2005-04-15   |  -500.00
 10 |           7 |          4 | 2005-05-15   |  -500.00
 11 |           7 |          4 | 2005-06-14   |  -500.00
 12 |           7 |          4 | 2005-07-15   |  -500.00
 13 |           7 |          4 | 2005-09-25   | -2500.00
 14 |           7 |          4 | 2005-09-25   |  4750.00
 15 |           7 |          5 | 2005-09-23   |  2500.00

Notice that we have taken the items that made up the natural key and put them into separate columns in the surrogate key version. This improves the table design, by having only one value in each cell, using a descriptive word for the customer type, and recording the account opening date so that we can always calculate the account age.

Also we have included the legacy account number in the account table. This is not ideal, because it has the account_type embedded in it. It is possible (we should check) that there are some account numbers that are the same except for the type, so we could have C102764 and L102764. Moving to a numeric-only account number would be preferable, but it is not always feasible to make a sudden change to something that affects existing customers. If we are stuck with the old account numbers then we should regard them as simply identifiers and keep the account_type as a separate field, as shown in this data, and always use this to determine the type of account.

Allocating Surrogate Key Values

Surrogate keys are normally generated as sequences, so the surrogate key of the record you are adding is the next value from the sequence, for that column, in that table. Sequences normally start at 1 and increment by 1 for each new key. You can override this, but I have never seen a good reason to do so. Using the default assignment, you get over 2 billion identifiers from a 4-byte key (integer data-type in PostgreSQL). If you have more than 2 billion records in one table you can use an 8-byte key (bigint in PostgreSQL) which will give you 9,223,372,036,854,775,807 positive values and that will be enough.

Our keys may be allocated from a sequence but we must never try to infer anything from that. The sequence is just the most efficient way of making sure that every key is unique. If we try to infer the relative age of two records by comparing their ids we will make our code fragile and it will break eventually. Resist the temptation!

An Unwelcome Aside - UUIDs

We are assuming that your database is not distributed. Distributed databases have become fashionable, but they are needed only in extreme cases which are outside the requirements of very nearly all organisations. Distributed databases have many drawbacks, including the fact that they make sequences impractical, because you need a central store of the last-used number for each sequence, and with a distributed database you don't have a central store. As a result, distributed database developers have come to use large, randomly-generated, numbers using techniques which make the probability of generating the same number twice very, very low indeed. The most common type of these numbers are Universally Unique Identifiers ("UUIDs"). Microsoft, being averse to standards, calls these "GUID"s.

Examples of UUIDs are:

         46d49c83-48ec-4205-b3cd-af00ffd7f6d6
         db5aacc7-2787-4c65-81c2-265ebf6e733f
         51b8f63d-4c1b-4cc9-875d-97224a4c0f73

These are in their "human-readable" form; they are stored as 16-byte binary values. Even in the "human-readable" form, they are difficult for human beings to compare or transcribe, so many people simply compare them by looking at the first 6 and the last 6 digits and transcribe them using copy and paste. With random UUIDs, this would usually work, but this practice has led to an opportunity for malicious people with significant computer power to carry out a fraud known as "address poisoning". What the fraudsters do is to generate as many UUIDs as they need to, in order to find one with the same start and same end as the victim's UUID. They then send their own UUID to the victim, who checks the beginning and end, thinks it matches and sends money to the criminal's account. There have been many cases of this. "Cryptocurrency" accounts ("wallets") usually use even longer numbers, and several cases of "address poisoning" have been reported on Molly White's important website: "Web3 Is Going Great". Here are a few of them .

Cases such as this are spectacular, and happen far more frequently than we would hope (we'd prefer never), but the daily inconvenience of UUIDs is apparent to anyone using a data lake, such as Microsoft's "Dataverse", which is the data store for its Dynamics 365 business applications. UUIDs waste significant amounts of expensive storage, and lead to wasted time and mistakes for anyone trying to make sense of data in the "Dataverse"

The simple lesson is: use integer sequences, rather than UUIDs, to generate your surrogate keys.

Sample Data

Now we'll look at some sample data which will help us to see the practical differences between the natural key and surrogate key approaches.

A Query and Some Results on the Natural Key Model

select
    trim(a.first_name) || ' ' || trim(a.last_name) as customer,
    case
        when substring(b.customer_key from 13 for 1) = 'A' then 'Individual'
        when substring(b.customer_key from 13 for 1) = 'B' then 'Joint'
        when substring(b.customer_key from 13 for 1) = 'C' then 'Business'
        when substring(b.customer_key from 13 for 1) = 'D' then 'Charity'
    end as customer_type,
    b.account_type                     as account_type,
    b.account_key                      as account_number,
    c.created_date                     as transaction_date,
    c.amount
from
    customer a
    join
    account b
    on a.customer_key = b.customer_key
    join
    transaction c
    on
        b.customer_key = c.customer_key and
        b.account_key = c.account_key
order by
    customer,
    account_type,
    account_number,
    transaction_date;
    
     customer     | customer_type | account_type | account_number | transaction_date |  amount  
------------------+---------------+--------------+----------------+------------------+----------
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-03-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-04-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-05-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-06-14       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-07-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-09-25       | -2500.00
 Deborah Adnams   | Individual    | Current      | C635273        | 2005-09-25       |  4750.00
 Deborah Adnams   | Individual    | Loan         | L777253        | 2005-09-23       |  2500.00
 Jerome Howbridge | Individual    | Current      | C102764        | 2023-05-23       |    15.27
 Jerome Howbridge | Individual    | Current      | C102764        | 2023-05-23       |  -420.72
 Jerome Howbridge | Individual    | Current      | C102764        | 2023-05-24       |    24.67
 Jerome Howbridge | Individual    | Current      | C102764        | 2023-05-26       |   113.50
 Jerome Howbridge | Individual    | Current      | C102764        | 2023-05-27       |     4.52
 Jerome Howbridge | Individual    | Investment   | I534278        | 2016-03-31       | -1000.00
 Jerome Howbridge | Individual    | Savings      | S100234        | 2016-03-31       |  1400.00

A Query and Some Results on the Surrogate Key Model

select
    trim(a.first_name) || ' ' || trim(a.last_name) as customer,
    a.customer_type                                as customer_type,
    b.account_type                                 as account_type,
    b.legacy_account_number                        as legacy_account_number,
    c.created_date                                 as transaction_date,
    c.amount                                       as amount
from
    customer a
    join
    account b
    on a.id = b.customer_id
    join
    transaction c
    on b.id = c.account_id
order by
    customer,
    account_type,
    legacy_account_number,
    transaction_date;
    
     customer     | customer_type | account_type | legacy_account_number | transaction_date |  amount  
------------------+---------------+--------------+-----------------------+------------------+----------
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-03-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-04-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-05-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-06-14       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-07-15       |  -500.00
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-09-25       | -2500.00
 Deborah Adnams   | Individual    | Current      | C635273               | 2005-09-25       |  4750.00
 Deborah Adnams   | Individual    | Loan         | L777253               | 2005-09-23       |  2500.00
 Jerome Howbridge | Individual    | Current      | C102764               | 2023-05-23       |    15.27
 Jerome Howbridge | Individual    | Current      | C102764               | 2023-05-23       |  -420.72
 Jerome Howbridge | Individual    | Current      | C102764               | 2023-05-24       |    24.67
 Jerome Howbridge | Individual    | Current      | C102764               | 2023-05-26       |   113.50
 Jerome Howbridge | Individual    | Current      | C102764               | 2023-05-27       |     4.52
 Jerome Howbridge | Individual    | Investment   | I534278               | 2016-03-31       | -1000.00
 Jerome Howbridge | Individual    | Saving       | S100234               | 2016-03-31       |  1400.00

Some Advantages of Surrogate Keys

We can arrange things so that one key identifies one thing, forever.

Imagine that you are in a start-up company. Say this company is started by two people Freda and Bill, and they are successful and grow. They add people called James, Anji, Mike, Sandra, John, Liz. So far, if Freda asks you to talk to Sandra about a new product, you know who to go to. Then they add more people, Sally and Mike. So now if Freda asks you to talk to Mike, you have to say, "which one". Up until this point first name was a "candidate key"; everyone in the company had a first_name and they were all different. But now we need to add last_name or something to identify everyone individually. Say we choose to use first name and last name. Soon we get two Anji Wilson's, so we have to refine our "candidate key" again. Maybe we add hair colour, and then one Anji Wilson dyes her hair (and why shouldn't she?).

With people it can be quite charming to identify people by some characteristic (it can also be unpleasant, but we're all nice people, so we wouldn't do that).

In a database we need to identify people, and other things we record, precisely and reliably. We don't want to be sending money to the wrong Anji Wilson. In fact we do not want to muddle their records in any way at all. So, in the database we want a consistent, unambiguous key for everyone. In fact we want a consistent, unambiguous key for every thing that we record. Surrogate keys enable us to give every thing in our database a precise, unchanging, never-missing identifier. We record the other attributes (names, job-titles, start-dates, and so on); we are not removing these personal characteristics, we are just adding a unique key for each person.

We add to the other benefits of using surrogate keys by using a simple naming convention for primary and foreign keys.

The primary key is called id and is always defined as the first column in every table, like this:

 id integer primary key generated always as identity,

and a foreign key is always defined as

 <referenced table name>_id

Looking at the customer/account/transaction examples above:

the primary key of the account table, is named id
the foreign key in the transaction table, is named account_id

So, joins between these two tables will look like this:

 [...] from account join transaction on account.id = transaction.account_id

... and you can spend your time thinking about the problem you are trying to solve rather than the careless naming of columns by your vendor or "implementation partner".

If there are two or more foreign key references to the same table, then we have to add something to the name. For example, a transfer of money from one account to another would have two foreign keys from the transaction table to the account table and these might be from_account_id and to_account_id. This is the only reason why I would add anything to the foreign key name.

If you have ever had to interrupt your thought process to rummage through two Microsoft Dynamics tables, each with a few hundred columns, to find, eventually, that the foreign key fsdyn_subscriptioninstance.fsdyn_customer matches the primary key contact.contactid, and that contact.fsdyn_contactnumber is something completely different, then you will appreciate the consistent, predictable , "on rails" approach recommended here.

Benefits of Surrogate Keys

We eliminate compound keys altogether.
We don't have to worry about multiple candidate keys because the surrogate key is the one and only key for each record.
Tables are a bit smaller, because all keys are 4-byte integers.
Performance is a bit better because comparing 4-byte integers is a bit more efficient than comparing strings.
Joins are a bit simpler, because they are only ever one condition rather than conditions for all the parts of a compound key with and to link them all together.
Natural keys have meaning in the real world, as well as their job of identifying the record. If we use someone's name in a natural key then there is likely to be significant impact in the system if they change their name. We would need to change the person record, and all the things that link to it: existing transactions, communication preferences, credit history, and so on. Changing existing transactions would be particularly bad, since financial transactions should be write-once, immutable. Any correction must be applied as a separate transaction to keep a complete and reliable audit trail.

With surrogate keys, any attribute, including a person's name, can be changed safely. The person_id in the transaction table would stay the same; the person record would show the name change.
Finally, we can ignore several of the higher "normal forms" because they apply only to compound keys or to tables with multiple candidate keys, and we do not have either of these things.

If you follow the naming convention above, then it it even simpler.

Terminology

One thing that puts people off relational databases is the terminology. I understand, I feel the same way. But we should recognise that these things were invented by mathematicians, and they spoke in their own arcane language, some of it introduced specifically to help them understand how structured data works. Bear with us, with the benefit of the mathematicians' work, we can explain what is needed in more straightforward, everyday language.

Normalisation

Normalisation is a process of analysing data to remove various problems that can occur when you work with this data. You might get duplication, or two different versions of the truth for example. Normalisation analysis goes through a number of steps. Each step is described in terms of a problem that it removes. And the steps are:

relation
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Domain Key Normal Form (DKNF)
Sixth Normal Form (6NF)

If we use surrogate keys and we strictly make sure that all the columns in each table depend on the surrogate key and on nothing else then our database will satisfy all the normal forms.

But what does "depend on" mean? We will explain below.

If you want to read about all the normal forms, they are described in the references at the end of this page. But really, despite what many gurus say, "You Ain't Gonna Need It".

You do need to do the things described here. They will ensure that your database actually conforms to all the normal forms, and more importantly, you will avoid all the problems that the normal forms were developed to prevent.

Relation

A relation is a table. So why give it a funny name? Well, it is because relations are particular types of tables. A relation has a list of column names, and rows of column values. Each column has a specific data-type. All the data in one column, no matter how many rows there are, must be a valid value for that column. A particular cell, containing the column value for a particular row, may be empty ("null") and if it is not null it must be a valid value for the data-type of the column.

No row may be an exact copy another row; there can be no duplicate rows. I know of no database that enforces this, and the reason is that it would become more expensive, every time you add a new row, for the database to compare all the columns in the new row with all the columns in all the other rows in the table. In practice, though, this does not matter because we always provide a surrogate key, and this is generated in a way that does not produce duplicates, and we define that surrogate key as the primary key of the table and that is checked (efficiently) every time we add a new row.

But, you may say, if all the other columns, except the primary key, are the same as those in another row, then you have a duplicate, and that is almost always a problem. The page "Identifying Duplicate Data" explains effective and efficient ways to deal with this. Having a surrogate key makes it very easy to delete or correct one of the duplicates.

A column can be referenced only by its name, not by its sequence across the row. The "4th column" would be meaningless; we must use the names defined for this table. The database system knows where to find each column by name, but it may choose to store the columns in a different sequence. This is important because it means that we do not need to know how the data is stored; we simply refer to tables and columns by name. It also means that we can add a new column in the middle of a row, and our applications and queries will still work.

Similarly we cannot refer to a row by its position in the table, only by what it contains. We can ask for the rows to be presented to us in any order defined by values in one or more columns. But we cannot ask for the 7th row.

So, if our table follows these rules, then it is a relation. We will happily call it a "table" from now on.

First Normal Form

Reading C J Date again, I find some ambiguity because he says that every "relation" is in First Normal Form. But he goes on to talk about First Normal Form anyway. It doesn't matter: we get to the same place in the end.

A First Normal Form table must have no repeating groups. A repeating group could be a list of values in one cell, for example:

Recipe	Ingredients	cooking_minutes	method
Shortbread	flour, butter, sugar	20	1:mix 2:put in tin 3:flatten 4:bake

This example shows two repeating groups: the ingredients are a comma-separated list and the method is a numbered list. There would be other attributes, such as quantities and cooking temperature, of course.

To put this into first normal form, it should be modelled like this:

Now we can have as many ingredients as we like and as many steps as we like in the method. We could also do a much simpler search on particular ingredients.

Another common way of putting a repeating group into a table is to have a set of columns repeated, with the name of each column containing a number to identify the group, like this:

    Table "contact" includes the following columns:
  
    address1_city               
    address1_street              
    address1_postalcode         
    address1_telephone1         
    address1_telephone2
    address1_telephone3
    address2_city               
    address2_street              
    address2_postalcode         
    address2_telephone1         
    address2_telephone2
    address2_telephone3
    address3_city               
    address3_street           
    address3_postalcode         
    address3_telephone1         
    address3_telephone2
    address3_telephone3

This is a subset of a "tabular" representation of data about a person from a multi-billion dollar product being peddled by a huge software company. The full table has 447 columns, and 27 columns in each address group, so this is a small subset.

This has two levels of repeating groups: telephone numbers within addresses.

Suppose you have some person (or organisation) with four addresses. Too bad, there's no way to store the fourth address. And it is very out-dated to have telephone numbers linked to addresses. And why three per address? When you see numbered columns like this, it is a strong hint that something may be wrong.

Having had to write queries and reports on this data, I am clearly aware of the benefits of First Normal Form, which outlaws such bad design.

It should have been modelled like this:

These tables are now in First Normal Form.

Second and Third Normal Forms

Second and Third Normal Forms say that every column, except the key, must "depend on" the key. So what is the difference But what does "depend on" mean? Let's go back to our recipe database:

We've put measured_in in the ingredient table and this table stores the ingredients for a particular recipe.

The way we measure different ingredients depends on what they are. If they individual fruits or vegetables we might measure them by number (two large oranges, one onion, for example). If they are grains, or pasta, or other "dry goods" we might measure them by weight (100 grams of rice, 50 grams of sugar). It they are liquids we probably measure them by volume (20ml olive oil, 100ml wine). It is possible to convert between units, even between volume and weight if we also know the ingredient, But conversion is an extra option that we could add if we continue developing this recipe database. For now we will use just weight, volume and number. The value of measured_in is specific to the foodstuff, so they belong with the type of foodstuff, not with the use of an ingredient in a particular recipe. We could say that the value of measured_in depends on the type of foodstuff being used as an ingredient. Whereas, the quantity is specific to the ingredient in the recipe we are looking at.

So what we should have is this:

Joins

We started with everything in one file or table and we have so far turned this into three tables. That means we have to join them together again to present information to the user of the system. Some people, it seems, would rather pull their own finger nails out than have to write a join. Well, if you are using MongoDB or Microsoft's FetchXML, then I understand. But really, writing joins in SQL does not hurt! Organising your data properly in a relational database avoids ambiguity and duplication, saves space, improves consistency and improves performance. It's a bit like organising the clothes in your wardrobe: when you need a clean pair of socks, you know exactly where to find them. You don't have to pull out all the clothes, rummage through them, match odd socks and sniff them to make sure they are clean.

We write applications to let users see the data and manipulate it in ways that are convenient for them, and we store the data following the rules described here, to make sure that things are recorded and linked to one another correctly. We don't present the users with naked database tables, just as we would not present them with naked JSON documents.

Third Normal Form

The Normal Forms were summed up as:

Every column must be dependent on the key, the whole key and nothing but the key so help me Codd.

Other Database Design Advice

Do Not Encode Short Strings

Way back when a big computer disk was 8 megabytes, we had to do all sorts of things to save space. A common technique was to use code tables for short text values. So if you had values, in this example colour names, you could have a table:

Code	Value
A	Red
B	Blue
C	Yellow
D	Purple
E	Green
F	Orange

You could then store the letter for each colour in your detail records, and look up the letters in the colour table. If you had 100,000 records referring to colour, it would take 100,000 bytes to record a colour for every record. If you stored the colour names you would need 500,000 bytes. That was a big deal 50 years ago.

For sets of values that are short character strings, it makes much more sense to store the actual values and drop the code/value table altogether. And when you want to retrieve the data, you don't have to look up any codes. You lose a little bit of space and you gain processing time because you don't have to look up the values.

You may want to be sure that the values that can be stored in the colour column are limited to a particular set. You can easily do this with a "check constraint" like this. The database will then check the data for you every time a row is added or changed. You don't need another table. Here's the code:

   
create table no_lookups
(
    id      integer primary key generated always as identity,
    colour  varchar(10),
    constraint standard_colours 
        check(colour in ('Red', 'Blue', 'Yellow', 'Purple', 'Green', 'Orange'))
);

But what if you need to add another value? You change the check constraint, like this:

alter table no_lookups
drop constraint standard_colours,
add constraint standard_colours 
    check(colour in ('Red', 'Blue', 'Yellow', 'Purple', 'Green', 'Orange', 'Brown'));

But what if your DBAs Database Administrators do not allow you to change metadata? This is an unacceptable working arrangement.

Many lookups are simply boolean values (true or false; yes or no). For these, we should always use the boolean data-type. This has been available in PostgreSQL for ever (well, at least since 2001).

Oracle added a boolean type in release 23i (2023).

Microsoft SQL Server has a "bit" data-type which can be used as a boolean.

MariaDB has a boolean data-type which is a synonym for a "tinyint;"; tinyint is a signed one-byte number (-127 to +127) so this can be used as a boolean, but is rather fragile. Zero is false, null is "unknown" anything else is true. Use it with caution.

We must name boolean columns so that the value of the boolean tells us obviously and unambiguously what the meaning is.

For example, if we have a column that is called security_flag in an employee table, then does "true" mean that this person is considered a security risk, or does it mean that the person is approved for work requiring security clearance? If we name the column security_approved then it will be much more obvious what "true" and "false" mean.

In Microsoft Dynamics, they still have codes and values, and they store both in every record, as well as in the code table. Often the code is longer than the value (just one case from a real system: 2763049 is the code for the character string "No").

Of course, if your code represents something that is a whole record, with two or more fields, then that thing should have its own record and the "code" will actually be the key of that record. (The "code" should be a surrogate key in such cases.) This is normal database practice. Every financial transaction has the keys of the parties to that transaction, and the parties are stored in a separate table: each party stored once, with its key.

When Microsoft has codes which are the keys of records, they store the code and another field from the reference record (such as a customer's name) in the transaction record. They should store only the key, because they can find the name (and other details) in the record referenced by the key. Storing the name adds another column, wastes space and is duplication of data. Duplication is bad because if a name - an account-holder's name, for example - is stored in many places, then when it changes (as they do from time to time) it has to be changed everywhere. Very often, the change is not propagated, so we have conflicting data, and we may not know which is the correct, current version. This is a frequent cause of data quality issues, and causes customers irritation, or worse.

In general, we should eliminate code/value tables from our database, entirely.