🧩 Relational Database Design

Why Design Matters

Relational databases are the backbone of most business software — e-commerce, banking, healthcare, and beyond. Their staying power comes from a simple, mathematically grounded idea: store data in tables and connect those tables through shared values.

💡 Design is architecture. A good schema is like a well-planned building — it supports growth and change gracefully. A poor one leads to duplicated data, contradictory records, and queries that get slower every month. The good news: a handful of principles prevent almost all of it.

We'll build up those principles in order: the model, then a way to diagram it, then keys and relationships, then normalization to keep it clean.

The Relational Model

The relational model, introduced by E. F. Codd at IBM in 1970, organizes data into relations (tables). Each table has:

• Tuples (rows) — individual records, one per real-world instance
• Attributes (columns) — the properties of each record, each with a data type

Well-formed relations obey a few rules: each cell holds a single atomic value (never a list), no two rows are identical, every value in a column shares the same type, and column/row order carries no meaning.

In practice you create tables with SQL. Here it is in PostgreSQL:

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,          -- auto-incrementing surrogate key
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(100) UNIQUE NOT NULL,
    date_joined  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10, 2) NOT NULL
);

Entity-Relationship Modeling

Before writing a single CREATE TABLE, designers sketch an Entity-Relationship (ER) diagram — a visual map of the data that's easy to discuss with teammates and stakeholders. Its pieces:

• Entities — the things you store data about (customers, orders, products)
• Attributes — the properties of each entity
• Relationships — how entities connect, and with what cardinality

Here's an ER diagram for a small store, drawn with Mermaid:

Turning an ER diagram into a schema follows a reliable recipe:

1. Each entity becomes a table.
2. Each attribute becomes a column.
3. The identifying attribute becomes the primary key.
4. One-to-many relationships become a foreign key on the "many" side.
5. Many-to-many relationships become a junction table.

Keys & Referential Integrity

Keys are how relational databases identify records and link tables together.

Referential integrity

A foreign key constraint guarantees that every reference points to a row that actually exists — you can never have an order for a customer who isn't in the database. You also decide what happens when the referenced row is deleted or updated:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE     -- deleting a customer removes their orders
        ON UPDATE CASCADE
);

Modeling Relationships

Three relationship shapes cover almost everything.

One-to-many (1:N)

The most common shape: one customer has many orders, but each order belongs to one customer. Put the foreign key on the "many" side (orders.customer_id). We already did this above.

Many-to-many (N:M)

A student enrolls in many courses; a course has many students. Relational tables can't store this directly, so you introduce a junction table whose composite key pairs the two sides — and which can carry its own attributes (like a grade):

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    credits   INTEGER NOT NULL
);

-- Junction table resolves the many-to-many relationship
CREATE TABLE enrollments (
    student_id      INTEGER NOT NULL REFERENCES students(student_id),
    course_id       INTEGER NOT NULL REFERENCES courses(course_id),
    enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    grade           VARCHAR(2),
    PRIMARY KEY (student_id, course_id)   -- composite key: one enrollment per pair
);

One-to-one (1:1)

Rarer — used to split rarely-used or sensitive columns into a separate table. Put a foreign key that's also UNIQUE on the dependent side (e.g. a user_profiles table with a unique user_id).

Normalization (1NF → 3NF)

Normalization reorganizes tables to eliminate redundancy so every fact lives in exactly one place. Cramming everything into one wide table causes four classic problems:

• Redundancy — the same customer address repeated on every order
• Update anomalies — changing that address means editing many rows
• Insertion anomalies — you can't add a customer who has no order yet
• Deletion anomalies — deleting the last order erases the customer

First Normal Form (1NF) — atomic values

Every cell holds one value; no repeating groups or comma-lists. A phone_numbers column holding "555-1234, 555-5678" violates 1NF. Fix it by moving phones to their own table, one row per number.

Second Normal Form (2NF) — no partial dependencies

Be in 1NF, and every non-key column must depend on the whole composite key, not part of it. In a table keyed by (student_id, course_id), storing student_name is a partial dependency (it depends only on student_id). Split names into a students table.

Third Normal Form (3NF) — no transitive dependencies

Be in 2NF, and no non-key column may depend on another non-key column. If an order-items table stores category_name, and category name depends on category_id (a non-key), that's transitive — move categories into their own table.

Hands-on Exercise

loans(loan_id, book_title, author_name, author_born,
      member_name, member_email, borrowed_on, due_on)

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    born      DATE
);

CREATE TABLE books (
    book_id   SERIAL PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(author_id)
);

CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    email     VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE loans (
    loan_id     SERIAL PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(book_id),
    member_id   INTEGER NOT NULL REFERENCES members(member_id),
    borrowed_on DATE NOT NULL DEFAULT CURRENT_DATE,
    due_on      DATE NOT NULL
);

Best Practices

Summary & Quiz

📚 Further Reading

• PostgreSQL — Data Definition (tables, keys, constraints)
• MySQL — Foreign Key Examples
• Use The Index, Luke! — indexing for developers
• "Database Design for Mere Mortals" by Michael J. Hernandez (book)

🚀 What's Next?

You can now model structured data cleanly. But not all data is neat and tabular — next we cross into the other world. Up next: NoSQL Database Principles.