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)
);