Database Design Patterns

Good schema design prevents performance problems and maintenance headaches before they start. This lesson covers normalisation, denormalisation, key strategies, soft deletes, audit trails, and polymorphic associations.

---

Normalisation

Normalisation eliminates redundancy and ensures data integrity by organising data into related tables.

First Normal Form (1NF)

Each column contains a single, atomic value. No repeating groups.

-- Violates 1NF: multiple phone numbers in one column
CREATE TABLE contacts_bad (
  id    SERIAL PRIMARY KEY,
  name  TEXT,
  phones TEXT  -- '020-1234, 020-5678'
);

-- 1NF: separate table for phone numbers
CREATE TABLE contacts (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE phone_numbers (
  id         SERIAL PRIMARY KEY,
  contact_id INT REFERENCES contacts(id),
  phone      TEXT NOT NULL,
  label      TEXT  -- 'home', 'work', 'mobile'
);

Second Normal Form (2NF)

All non-key columns depend on the entire primary key (relevant for composite keys).

-- Violates 2NF: student_name depends only on student_id, not on (student_id, course_id)
CREATE TABLE enrolments_bad (
  student_id   INT,
  course_id    INT,
  student_name TEXT,  -- depends only on student_id
  grade        CHAR(1),
  PRIMARY KEY (student_id, course_id)
);

-- 2NF: separate the partial dependency
CREATE TABLE students (
  id   INT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE enrolments (
  student_id INT REFERENCES students(id),
  course_id  INT REFERENCES courses(id),
  grade      CHAR(1),
  PRIMARY KEY (student_id, course_id)
);

Third Normal Form (3NF)

No transitive dependencies — non-key columns must not depend on other non-key columns.

-- Violates 3NF: city depends on postcode, not directly on the primary key
CREATE TABLE addresses_bad (
  id       SERIAL PRIMARY KEY,
  street   TEXT,
  postcode TEXT,
  city     TEXT  -- transitively depends on postcode
);

-- 3NF: extract the dependency
CREATE TABLE postcodes (
  postcode TEXT PRIMARY KEY,
  city     TEXT NOT NULL
);

CREATE TABLE addresses (
  id       SERIAL PRIMARY KEY,
  street   TEXT,
  postcode TEXT REFERENCES postcodes(postcode)
);

---

Denormalisation

Sometimes you intentionally break normal forms for performance:

Technique	Description	Trade-off
Materialised views	Pre-computed query results	Stale data
Redundant columns	Copy a frequently-accessed value	Write overhead
Summary tables	Pre-aggregated data	Maintenance needed
JSONB columns	Embed nested data in a single column	Harder to query

Materialised Views

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  category,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY DATE_TRUNC('month', order_date), category;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

-- Must have a unique index for CONCURRENTLY
CREATE UNIQUE INDEX ON mv_monthly_sales (month, category);

---

Surrogate vs Natural Keys

Key Type	Pros	Cons
Surrogate (SERIAL, UUID)	Immutable, no business meaning changes	No inherent meaning, extra column
Natural (email, ISBN)	Self-documenting, no extra column	Can change, may be composite

UUID Primary Keys

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE orders (
  id    UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  total NUMERIC(10,2)
);

Tip: UUIDs prevent enumeration attacks and work well for distributed systems, but they are larger (16 bytes vs 4 bytes for INT) and have worse index locality. Consider uuid_generate_v7() (time-ordered) for better index performance.

---

Soft Deletes

Instead of deleting rows, mark them as deleted: