Relational Concepts and Normalization

What it is

A relational database stores structured data in tables. Each table describes one kind of thing, such as customers or orders, and tables are linked to each other by keys.

Normalization is the practice of splitting data into well designed tables so that each fact is stored only once.

How it works

Tables, rows and columns

A table is a set of rows and columns. A row is one record, such as a single customer. A column is one attribute, such as the customer's city, and every value in a column has the same data type. The set of tables, columns and types is called the schema.

Keys and relationships

A primary key is a column, or set of columns, whose value uniquely identifies each row. It cannot be empty and cannot repeat. A foreign key is a column in one table that holds the primary key value of a row in another, which creates the link between them.

The Customers table has CustomerID as its primary key.

The Orders table has OrderID as its primary key and CustomerID as a foreign key.

Here one customer has many orders, and each order points back to one customer.

Types of relationship

A many to many relationship is built with a third table, called a junction table, that holds the keys of both sides. A table of enrolments with a StudentID and a CourseID joins students to courses.

Constraints

Constraints are rules the database enforces on its own.

• NOT NULL: the column must hold a value.
• UNIQUE: no two rows may share the value.
• PRIMARY KEY: unique and not null, and it identifies the row.
• FOREIGN KEY: the value must exist in the referenced table.
• CHECK: the value must satisfy a condition, such as a quantity above zero.
• DEFAULT: a value used when none is supplied.

Normalization

Imagine one wide table that repeats the customer's name and address on every order line. If the customer moves, you must change many rows, and if you miss one the data disagrees with itself. Normalization removes that risk by moving each kind of fact into its own table.

The benefits are less duplicated data, fewer update mistakes and better integrity. The cost is that a query needs joins to bring the tables together again. For that reason analytical stores sometimes denormalise on purpose, while transactional systems stay normalised.

A worked scenario

A school stores each exam result in one row with the student's name, the teacher's name and the teacher's phone number. The phone number depends on the teacher, not on the result, so it breaks third normal form. Moving students, teachers and results into three tables linked by keys fixes it, and a change of phone number becomes a single update.

The same design as SQL

The two tables from the example could be defined like this. The statements show a primary key, a foreign key and several constraints in one place.

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name       VARCHAR(80) NOT NULL,
  Email      VARCHAR(120) UNIQUE
);

CREATE TABLE Orders (
  OrderID    INT PRIMARY KEY,
  CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
  Total      DECIMAL(10,2) CHECK (Total >= 0)
);

The REFERENCES clause makes CustomerID a foreign key. The database now refuses an order for a customer that does not exist, and refuses to delete a customer who still has orders unless a rule says otherwise. This protection is called referential integrity.

Choosing a primary key

A key can be a natural value that already exists in the data, such as a national product code, or a surrogate key that the database generates, such as an automatic number. Surrogate keys are short and never change, which makes them popular. A good primary key is unique, never empty and stable over time.

Relational data compared with other shapes