Some notes on SQL: 5 - database design

This is the fifth in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database, the third commands to modify the structure and contents of an existing database, the fourth on advanced selection. This post covers database design, as such it is a little lighter on the code examples. No claim of authority is made for these posts, they are mainly intended as my notes on the topic. These notes are based largely on Head First SQL.

The goal of database design is to produce a database which is straightforward and efficient to search. This is done by splitting data into a set of tables, with lookups between those tables used to build the desired output results.

Efficient database design is normally discussed with reference to "normal forms", the goal being to reach the highest order normal form. In practice, pragmatism is applied which means it may be sensible to hold back a little on this.

First normal form - each row of data must contain atomic values, and each row of data must have a unique identifier, known as a Primary Key. "Atomic values" are essentially the smallest pieces into which data can be sensibly divided, this may depend on application. So, for example, in some cases a house address may be kept as a single text field whilst in others it might be divided into Number, Street, Town etc. Furthermore to be "atomic" data should not be repeated (i.e. a table containing interests should not contain columns "interest_1", "interest_2"...

The Primary Key may be a single column of 'synthetic' numbers (i.e. they don't have any other purpose), or it may be a pre-existing column in the table, or it may be a combination of columns which case it is called a Composite Key. Primary and Composite Keys are indicated using the PRIMARY KEY keyword :

CREATE TABLE customer
  (
     sid        INTEGER,
     last_name  VARCHAR(30),
     first_name VARCHAR(30),
     PRIMARY KEY (sid)
  );
For a composite key, this form is used:
PRIMARY KEY (column_1,column_2,column_3)

Second normal form the table is in first normal form, and in addition contains no 'partial functional dependencies', this happens naturally with synthetic primary keys. Partial functional dependency means that a non-key column is dependent on some but not all of the columns in a composite primary key.

Third normal form the table is in second normal form, and in addition contains no 'transitive dependencies'. Transitive functional dependency is when any non-key column is related to any of the other non-key columns. This page has a nice example, if we have a table with columns: {Project_id, manager_name, manager_address} then manager address and manager name are transitively dependent: change manager name and we change manager address. To address this in third normal form we split the table into two tables {Project_id, manager name} and {Manager_name, manager_address}. As the author writes:

In a normalised relation a non-key field must provide a fact about the key, the whole key and nothing but the key.

Relationships between tables in a database are indicated like this:

CREATE TABLE orders
  (
     order_id     INTEGER,
     order_date   DATE,
     customer_sid INTEGER,
     amount       DOUBLE,
     PRIMARY KEY (order_id),
     FOREIGN KEY (customer_sid) REFERENCES customer(sid)
  );


(Example borrowed from here). PRIMARY KEY and FOREIGN KEY are examples of 'constraints', primary keys must be unique and a foreign key value cannot be used in a table if it does not exist as a primary key in the referenced table. The CONSTRAINT keyword is used to give a name to a constraint (a constraint being one of NOT NULL, UNIQUE, CHECK, Primary Key, Foreign Key). CHECK is not supported in MySQL.


Keywords: PRIMARY KEY, CONSTRAINT, FOREIGN KEY, REFERENCES, CONSTRAINT