Relational design: keys and relationships

From entities to tables

Designing a relational database consists of identifying the entities of the domain (customers, orders, products...) and turning each one into a table, deciding its columns, its data types and how they relate.

Primary key (PK)

The primary key identifies each row of a table uniquely. It cannot be repeated nor be null.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,   -- identifies each customer unambiguously
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

A good PK is stable and without business meaning (a numeric id is usually better than the email or the ID number, which can change).

Foreign key (FK)

A foreign key is a column that points to the primary key of another table. It serves to connect tables and to make the database guarantee referential integrity: you won't be able to create an order for a customer that doesn't exist.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total REAL NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Data types

Each column has a type that defines which values it accepts. In SQLite the most used types are:

Choosing the right type avoids errors and improves performance and validation.

Types of relationship

1:1 (one to one)

Each row of A corresponds to at most one row of B and vice versa. For example, user and extended_profile. It is modeled by placing an FK with a UNIQUE constraint on one of the tables.

1:N (one to many)

The most common relationship: one row of A relates to many of B, but each row of B with a single one of A. A customer has many orders; each order belongs to one customer. It is modeled with an FK on the "many" side (orders.customer_id).

N:M (many to many)

Many rows of A relate to many of B. For example, orders and products: an order contains several products and a product appears in several orders. It cannot be represented with a single FK: a junction table (also called a bridge table or linking table) is needed, having an FK to each side.

CREATE TABLE order_line (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id),       -- composite key
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Rule of thumb: every N:M relationship is resolved with a junction table that contains the two FKs (and often data of its own about the relationship, such as the quantity).