<< Home

Chapter 3: Relational Database Concepts

At the heart of every relational database lies a simple yet powerful model: data organized into related tables. Each table represents one subject; each row represents one record; and relationships between tables preserve logical connections. This chapter introduces the essential components of the relational model (tables, keys, normalization, constraints, and referential integrity) that make relational databases consistent and reliable.

💡 The relational model is built on solid mathematical foundations, but you do not need to be a mathematician to use it. Think in terms of tables and relationships, and the logic will follow naturally.

Tables, rows, columns, keys, and relationships

Data in a relational database is stored in tables. Each table consists of rows (records) and columns (fields). Rows hold the actual data values, while columns define the data’s structure and constraints. A table’s design defines what kind of data is allowed and how it connects to other tables.

Tables and columns

Every table should describe one type of entity. Columns describe attributes of that entity, such as a person’s name or an order’s date. Each column has a data type that restricts the kind of values it can hold.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  email       TEXT    UNIQUE,
  joined_on   DATE    DEFAULT CURRENT_DATE
);

This table represents customers. Each column has a purpose: customer_id uniquely identifies the row, name is required, email must be unique, and joined_on defaults to the current date.

Rows and relationships

Each row is a single record, and relationships connect rows in one table to rows in another. By linking tables with keys, you can query data across multiple subjects without duplicating information.

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date  DATE    NOT NULL,
  total       NUMERIC NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Here, orders refers to customers using customer_id. The relationship is one-to-many: one customer can have many orders, but each order belongs to a single customer.

⚠️ Avoid storing the same data in more than one table. Redundancy makes updates harder and can create inconsistencies when one copy changes but others do not.

Normalization and denormalization

Normalization is the process of organizing data to reduce redundancy and improve integrity. It divides large, repetitive tables into smaller, related ones. Each table focuses on a single subject, and relationships connect them. The goal is to avoid anomalies when inserting, updating, or deleting data.

Normal forms

Several “normal forms” define stages of improvement. Each form builds upon the previous one. The first three are most commonly applied in practical design.

First Normal Form (1NF): Each column holds atomic (indivisible) values, and each row is unique.
Second Normal Form (2NF): Every non-key column depends on the entire primary key, not just part of it.
Third Normal Form (3NF): No non-key column depends on another non-key column.

-- Bad design: repeated address fields cause redundancy
CREATE TABLE employees (
  emp_id    INTEGER PRIMARY KEY,
  name      TEXT,
  dept_name TEXT,
  dept_addr TEXT
);

-- Better design: separate departments into their own table
CREATE TABLE departments (
  dept_id   INTEGER PRIMARY KEY,
  dept_name TEXT,
  dept_addr TEXT
);

CREATE TABLE employees (
  emp_id  INTEGER PRIMARY KEY,
  name    TEXT,
  dept_id INTEGER REFERENCES departments(dept_id)
);

💡 Normalization helps ensure that each fact is stored only once. Queries may become longer, but updates and consistency checks become much easier.

Denormalization

Denormalization deliberately combines related data for performance or simplicity. It reduces joins by storing precomputed or redundant information. While this can speed up queries, it increases the chance of inconsistencies if data changes in one place but not another.

For example, an orders table might include a customer_name column to avoid a join in frequent reports. If a customer changes their name, all affected orders must be updated to keep data correct.

⚠️ Normalize for integrity first; denormalize only when you have measured and proven a performance need.

Primary vs foreign keys

Keys are special columns (or sets of columns) that define relationships and uniqueness. The primary key uniquely identifies each row in its table. A foreign key references a primary key in another table, establishing a link between the two.

Primary keys

A primary key ensures that each record is distinct. It can be a single column or a combination. A good primary key never changes.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC
);

Each product has a unique product_id. The database will reject any attempt to insert a duplicate.

Foreign keys

A foreign key defines a relationship between two tables by referencing another table’s primary key. It enforces consistency between related records.

CREATE TABLE order_items (
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity   INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id),
  PRIMARY KEY (order_id, product_id)
);

Here, each item belongs to both an order and a product. The composite key (order_id, product_id) prevents duplicate entries for the same product in the same order.

Constraints

Constraints are rules that protect data integrity. They define what values are allowed, ensure required data is present, and maintain logical consistency. Most databases enforce constraints automatically at insert or update time.

Common constraint types

PRIMARY KEY: Ensures unique identification of each row.
FOREIGN KEY: Links tables and enforces referential integrity.
UNIQUE: Prevents duplicate values in a column.
NOT NULL: Requires a value for the column.
CHECK: Validates data against a condition.
DEFAULT: Supplies a fallback value when none is provided.

CREATE TABLE employees (
  emp_id   INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  salary   NUMERIC CHECK (salary >= 0),
  hired_on DATE DEFAULT CURRENT_DATE
);

If you try to insert a negative salary or omit the name, the database will reject the statement.

💡 Always define constraints close to the data. They serve as a built-in layer of validation, independent of any application code.

Deferrable and cascading constraints

Some databases allow constraints to be deferred until transaction commit. This lets you perform several related updates that temporarily violate rules but end up consistent when the transaction completes. Cascading actions automatically apply updates or deletions to related rows.

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

If a customer’s ID changes, related orders update automatically. If the customer is deleted, their orders remain but the customer_id becomes NULL.

Referential integrity

Referential integrity ensures that relationships between tables remain valid. Every foreign key must match an existing primary key or be NULL. This rule prevents orphaned records and maintains consistency across tables.

How it works

When you insert or update data, the database checks foreign keys to confirm that referenced rows exist. When you delete data, it checks for dependent rows that might be affected.

-- Insert succeeds only if the customer_id exists
INSERT INTO orders (order_id, customer_id, order_date, total)
VALUES (2001, 1, '2025-11-02', 199.99);

Cascading effects

To preserve referential integrity automatically, most systems support cascading actions. You can specify what should happen when a referenced row is changed or deleted:

ON UPDATE CASCADE – Update foreign keys when the parent key changes.
ON DELETE CASCADE – Delete dependent rows when the parent is deleted.
ON DELETE SET NULL – Set the foreign key to NULL if the parent is deleted.
ON DELETE RESTRICT – Prevent deletion if dependent rows exist.

CREATE TABLE order_items (
  order_id   INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(product_id),
  quantity   INTEGER NOT NULL
);

⚠️ Be careful with ON DELETE CASCADE. It can remove large amounts of data unintentionally if parent rows are deleted without proper checks.

Maintaining integrity over time

Referential integrity protects against data drift as applications evolve. Even if multiple programs or services write to the same database, properly defined keys and constraints ensure that the structure remains sound.

The principles of tables, keys, constraints, and relationships form the backbone of all relational databases. Mastering them will make every future SQL statement more reliable and every schema easier to maintain.