9. Schema design and data modelling¶

Time: ~2 hours Prerequisites: 03, 07

1. In plain words¶

A schema is the set of tables, columns, types and constraints that says what data is allowed to exist. A good schema makes wrong data impossible to store, makes the common queries cheap, and reads like documentation of the business. A bad schema lets the same fact live in three places (and disagree), forces every query through five joins, or stores dates as strings.

The analogy is a set of paper forms designed for a bank branch. If the customer's address is written on the loan form, the account form and the card form, one of them will be wrong after she moves. If the form has a box labelled "status" where clerks write anything, reports break. Normalisation is the discipline of "each fact in exactly one place"; constraints are the printed rules on the form ("tick exactly one"); indexes are the filing order.

This module models the loan-origination domain used by every code example in the course, and explains each decision as you would in a design review.

2. The real thing¶

2.1 Normalisation to 3NF in plain words¶

1NF: every column holds one atomic value; no repeating groups (phone1, phone2, phone3), no comma-separated lists in a text column. Repeating things become rows in a child table.
2NF: every non-key column depends on the whole primary key. Matters for composite keys: in (application_id, document_id, customer_name) the customer name depends only on the application, so it belongs on the application (or the customer), not here.
3NF: every non-key column depends on the key, the whole key, and nothing but the key: no column depends on another non-key column. branch_code, branch_name on the application row violates it (branch_name depends on branch_code); move branch_name to branches.

In practice: identify the entities (customer, branch, application, document, user), give each a table, store each fact once, and link with foreign keys. Then denormalise deliberately only where measured reads need it (a cached application_count on customers, a current_status copied from the latest audit row), and document how the copy is kept consistent (trigger, or the procedure that owns the write).

Your migration source was the opposite: Berkeley DB records were packed structs with everything inlined (customer name inside the loan record, because joins did not exist). Mapping those records to relational tables is normalisation, and "record-to-relational mapping" on your resume means: decompose each packed record into 3NF entities, choose keys, and keep the reverse mapping for parity checks (module 12).

2.2 Keys: surrogate versus natural¶

A natural key is a business identifier that already exists (national ID, branch code + user code, application number). A surrogate key is a meaningless identifier the database generates (bigint GENERATED ALWAYS AS IDENTITY, or a UUID).

	Natural key as PK	Surrogate key as PK
Stability	business rules change (codes get renumbered, IDs get reissued); changing a PK cascades through every FK	never changes
Size	often text, 10 to 40 bytes, in every child table and index	8 bytes
Joins	self-describing child rows	one more join to show the code
Uniqueness	must still be enforced	natural key still needs a UNIQUE constraint

The convention that works: surrogate bigint identity as the primary key, natural key as a UNIQUE NOT NULL constraint. Both get an index; both are usable for lookups. Your login bug (module 03) was not caused by having a surrogate key; it was caused by forgetting the unique constraint on the natural key, which would have created the index automatically. Say it that way.

UUIDs as PKs: fine when ids are generated by many clients or must not be guessable; use time-ordered UUIDv7 (or uuid_generate_v1mc) rather than random v4 to avoid B-tree splits everywhere (module 03). 16 bytes versus 8.

Composite primary keys are right for pure link tables (user_roles (user_id, role_id)), where no other table references the row.

2.3 Constraints as documentation¶

Every constraint is a sentence about the business that the database enforces for every writer, including the DBA with psql at 3 AM:

requested_amount numeric(18,2) NOT NULL CHECK (requested_amount > 0),
term_months      int           NOT NULL CHECK (term_months BETWEEN 6 AND 360),
status           text          NOT NULL REFERENCES workflow_states(code),
CONSTRAINT la_submitted_needs_time CHECK (status = 'DRAFT' OR submitted_at IS NOT NULL),
CONSTRAINT la_app_no_format CHECK (application_no ~ '^LA-[0-9]{4}-[0-9]{6}$'),
-- one active application per customer per product: a partial unique index
CREATE UNIQUE INDEX la_one_active ON loan_applications (customer_id, product_id)
    WHERE status NOT IN ('REJECTED', 'WITHDRAWN', 'CLOSED');
-- no overlapping validity periods for an interest rate: exclusion constraint (GiST)
CONSTRAINT rate_no_overlap EXCLUDE USING gist (product_id WITH =, valid_during WITH &&)

NOT NULL everywhere a value is mandatory; NULL means "unknown", nothing else. DEFERRABLE INITIALLY DEFERRED on foreign keys only when you must insert circular references in one transaction. Name constraints (CONSTRAINT la_amount_positive CHECK ...) so error messages and migration scripts are readable.

2.4 Foreign keys and their indexes¶

A foreign key is checked on insert/update of the child (an index lookup on the parent's PK, which exists) and on delete/update of the parent (a scan of the child table for referencing rows, which needs an index you must create). Every FK column that is deleted-through or joined-on gets an index; this is the most common missing-index class in real schemas:

SELECT conrelid::regclass AS child, a.attname AS fk_column
FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (SELECT 1 FROM pg_index i
                  WHERE i.indrelid = c.conrelid AND (i.indkey::int2[])[0] = a.attnum);

ON DELETE options: RESTRICT/NO ACTION (default; refuse) for most business data, CASCADE for true children (documents of an application), SET NULL for optional references. Cascading deletes across large child sets happen row by row under locks; prefer soft deletes for anything with history.

2.5 Enums versus lookup tables¶

	`CREATE TYPE status AS ENUM (...)`	lookup table + FK
Storage	4 bytes	text code (or small int FK)
Adding a value	`ALTER TYPE ... ADD VALUE` (cannot be run inside a transaction block before 12; cannot remove or reorder values)	`INSERT`
Attaching data to a value (display name, sort order, who may set it)	no	yes: columns on the lookup row
Portability	PostgreSQL-specific	standard

Rule: enums for small fixed sets that are truly part of the code ('DEBIT'/'CREDIT'); lookup tables for anything the business will extend or that needs attributes (workflow states, document kinds, rejection reasons). Text codes as FK values (status text REFERENCES workflow_states(code)) keep rows readable without joins and cost a few bytes more than a smallint.

2.6 Soft deletes and audit tables¶

Soft delete (deleted_at timestamptz NULL) keeps the row for history and FK integrity. Costs: every query needs WHERE deleted_at IS NULL (hide it behind a view or row-level security), unique constraints must become partial (UNIQUE ... WHERE deleted_at IS NULL), and "deleted" rows still occupy pages and indexes. Use it for business entities (customers, applications); hard-delete transient data (sessions, tokens).

Audit: two patterns. A per-table history table written by a trigger (loan_applications_history with valid_from/valid_to and the full row image), good for "show me the application as of 1 March"; or an append-only event table (application_audit with from_state, to_state, actor, note, time), good for "who did what". Store the actor from the application (updated_by column or set_config('app.actor_id', ...) read by the trigger), because the database user is the pooled application role, not the clerk. Audit tables are insert-only: REVOKE UPDATE, DELETE from everyone but the owner.

2.7 Time zones¶

Use timestamptz ("timestamp with time zone") for every instant. It is stored as 8-byte UTC microseconds; the session TimeZone setting only affects display and parsing. timestamp without time zone is a wall-clock reading with no instant attached: two servers in different zones store different instants for "now".
Use date for calendar dates (date of birth, due date). Due dates are business-day concepts: "the 5th" is not an instant.
Store the customer's or branch's IANA zone ('Asia/Kolkata') if you need local rendering; never a fixed offset, because offsets change with DST and law.
now() is the transaction start time (stable within a transaction); clock_timestamp() is the wall clock. Audit rows should use now() so all rows of one transaction share a timestamp.
Monthly scheduling: date_trunc('month', ts AT TIME ZONE 'Asia/Kolkata'), computed in the business zone, not in UTC, or month boundaries land at 05:30.

2.8 Types: the short list¶

bigint for ids and counters (not int: 2.1 billion arrives), numeric(18,2) for money (never float), text (not varchar(n) unless the limit is a business rule; add a CHECK (length(x) <= n) which is easier to change), boolean not char(1), timestamptz, date, uuid, jsonb for genuinely schemaless attachments (with a GIN index when queried), bytea for binary blobs under a few MB (bigger: object storage with a reference). Order columns for alignment (module 02).

2.9 The loan-origination model¶

 branches ---< app_users >--< user_roles >--- roles
    |             |
    |             | (actor)
    |             v
 customers ---< loan_applications ---< documents
    ^               |      |
    |               |      +---< payments
    +-- (kyc)       |
                    +---< application_audit
 loan_products ---< loan_applications
 workflow_states ---< workflow_transitions >--- roles (required_role)
 workflow_states <--- loan_applications.status

Entities and key decisions:

branches(code PK text): a natural key is the PK here because branch codes are short, stable, printed on every document, and referenced by name everywhere. One deliberate exception.
customers(id identity PK, national_id UNIQUE, email UNIQUE partial where deleted_at IS NULL, kyc_verified, deleted_at).
app_users(id PK, branch_code FK, user_code, UNIQUE (branch_code, user_code), password_hash, status, failed_attempts, last_login_at): the login natural key as a unique constraint; see module 03 for the covering index.
roles(code PK), user_roles(user_id, role_id, branch_code) PK (user_id, role_id, branch_code).
loan_products(id PK, code UNIQUE, max_amount, ...).
loan_applications(id PK, application_no UNIQUE, customer_id FK, product_id FK, branch_code FK, status FK workflow_states, requested_amount, term_months, submitted_at, created_at, updated_at, updated_by FK app_users).
documents(id PK, application_id FK ON DELETE CASCADE, kind FK document_kinds, storage_key, sha256 bytea, uploaded_at); content in object storage, not in the row.
payments(id PK, application_id FK, kind ('DISBURSEMENT','REPAYMENT'), amount, posted_at).
workflow_states(code PK, is_terminal), workflow_transitions(from_state, to_state, required_role, PK (from_state, to_state)): the state machine is data, so adding a state is an INSERT, and the procedure in module 08 reads it.
application_audit(id PK, application_id FK, from_state, to_state, actor_id, note, at timestamptz default now()): insert-only.
accounts and on_call: tiny tables for the isolation demos in module 05.

Index set (beyond PK/UNIQUE): every FK column; (status, submitted_at) for work queues; (customer_id, submitted_at DESC) for "latest per customer"; (submitted_at, id) for keyset pagination; partial unique for one active application per customer and product.

3. Code¶

code/m09_schema.sql creates everything above with comments on each decision; code/m09_seed.sql loads 500 branches, 200k customers, 2 M users (the module 03 table), 1 M applications, documents and payments, using generate_series so it runs in a few minutes on a laptop. Key excerpts:

CREATE SCHEMA IF NOT EXISTS loans;
SET search_path = loans, public;

CREATE TABLE branches (
    code             text PRIMARY KEY CHECK (code ~ '^BR[0-9]{4}$'),   -- natural key, deliberately
    name             text NOT NULL,
    max_loan_amount  numeric(18,2) NOT NULL CHECK (max_loan_amount > 0),
    time_zone        text NOT NULL DEFAULT 'Asia/Kolkata'              -- IANA zone, not an offset
);

CREATE TABLE customers (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    national_id   text NOT NULL,
    full_name     text NOT NULL,
    email         text,
    date_of_birth date NOT NULL CHECK (date_of_birth > DATE '1900-01-01'),
    kyc_verified  boolean NOT NULL DEFAULT false,
    created_at    timestamptz NOT NULL DEFAULT now(),
    deleted_at    timestamptz,                                         -- soft delete
    CONSTRAINT customers_national_id_key UNIQUE (national_id)
);
CREATE UNIQUE INDEX customers_email_live_key ON customers (lower(email)) WHERE deleted_at IS NULL;

CREATE TABLE loan_applications (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    requested_amount numeric(18,2) NOT NULL CHECK (requested_amount > 0),   -- 8-byte-aligned fields first
    customer_id      bigint NOT NULL REFERENCES customers(id),
    product_id       bigint NOT NULL REFERENCES loan_products(id),
    updated_by       bigint REFERENCES app_users(id),
    submitted_at     timestamptz,
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    term_months      int NOT NULL CHECK (term_months BETWEEN 6 AND 360),
    application_no   text NOT NULL,
    branch_code      text NOT NULL REFERENCES branches(code),
    status           text NOT NULL DEFAULT 'DRAFT' REFERENCES workflow_states(code),
    CONSTRAINT la_application_no_key UNIQUE (application_no),
    CONSTRAINT la_application_no_format CHECK (application_no ~ '^LA-[0-9]{4}-[0-9]{6}$'),
    CONSTRAINT la_submitted_needs_time CHECK (status = 'DRAFT' OR submitted_at IS NOT NULL)
);
CREATE INDEX la_customer_submitted_idx ON loan_applications (customer_id, submitted_at DESC);
CREATE INDEX la_status_submitted_idx   ON loan_applications (status, submitted_at);
CREATE INDEX la_submitted_id_idx       ON loan_applications (submitted_at, id);
CREATE INDEX la_product_idx            ON loan_applications (product_id);
CREATE INDEX la_updated_by_idx         ON loan_applications (updated_by);
CREATE UNIQUE INDEX la_one_active_key  ON loan_applications (customer_id, product_id)
    WHERE status NOT IN ('REJECTED', 'WITHDRAWN', 'CLOSED');

CREATE TABLE application_audit (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    application_id bigint NOT NULL REFERENCES loan_applications(id),
    actor_id       bigint REFERENCES app_users(id),
    at             timestamptz NOT NULL DEFAULT now(),                -- transaction time, not clock time
    from_state     text NOT NULL,
    to_state       text NOT NULL,
    note           text
);
CREATE INDEX application_audit_app_idx ON application_audit (application_id, at);
REVOKE UPDATE, DELETE ON application_audit FROM PUBLIC;

The audit trigger pattern (statement-level, set-based) is also in the schema file:

CREATE OR REPLACE FUNCTION trg_touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END $$;
CREATE TRIGGER la_touch BEFORE UPDATE ON loan_applications
    FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION trg_touch_updated_at();

4. Under the hood (senior-level)¶

What a constraint costs. NOT NULL and CHECK are evaluated in the executor per row, microseconds. UNIQUE/PRIMARY KEY are B-tree indexes with uniqueness checks at insert time, including waiting on concurrent uncommitted inserters of the same key (module 03). FOREIGN KEY is implemented as system triggers (RI_FKey_check_ins, RI_FKey_noaction_del ...) that run a cached SPI query (SELECT 1 FROM parent WHERE pk = $1 FOR KEY SHARE) per changed row; the FOR KEY SHARE lock on the parent is why heavy inserts of children contend on a hot parent row (MultiXacts, module 05). Statement-level FK checking with transition tables is still not in 16, so a bulk insert of 1 M children runs 1 M parent lookups: fast with the parent's PK index, but not free. Loading with ALTER TABLE ... DISABLE TRIGGER ALL is an option when the data is known good and you accept the responsibility.

Identity and sequences. GENERATED ALWAYS AS IDENTITY creates a sequence owned by the column. nextval() is non-transactional: a rolled-back insert leaves a gap. Sequences cache values per session (CACHE 1 default) and are WAL-logged every 32 values, which is why sequence values can jump after a crash. Gaps are normal; never promise gapless numbers from a sequence. For application_no with a per-year gapless counter, use a counter table row updated under a row lock, accepting the serialisation point. On YugabyteDB, sequences are distributed and cache 100 values per node by default, so ids are not monotonic across nodes (module 11).

ALTER TABLE lock classes. ADD COLUMN with no default or a constant default: catalog-only, ACCESS EXCLUSIVE for milliseconds. ADD COLUMN ... DEFAULT now() (volatile): rewrites the table. ALTER COLUMN TYPE: usually a rewrite plus index rebuilds (except binary-compatible changes such as varchar(50) to text). ADD CONSTRAINT CHECK/FK: scans the table under a lock, unless NOT VALID then VALIDATE CONSTRAINT (which takes only SHARE UPDATE EXCLUSIVE). SET NOT NULL: full scan, unless 12+ with an existing valid CHECK (col IS NOT NULL). Knowing which operations rewrite is what lets you promise "zero downtime" for a migration.

Why text and not varchar(n). Both are the same varlena type internally; varchar(n) adds a length check at input. Changing n later on a varchar column is a catalog-only change since 9.2 when growing, but the limit lives in the type, so views, functions and dependent columns inherit it. A CHECK constraint is explicit, named, and droppable.

Partitioning (declarative, 10+): split a huge table by range (created_at by month), list (branch_code), or hash. Benefits: dropping a month is DROP TABLE of a partition instead of a 10 M row delete; partition pruning reads only relevant partitions; smaller indexes per partition. Costs: planning time grows with partition count (hundreds, not thousands), unique constraints must include the partition key, no global indexes. Use it for audit/event tables past a few hundred GB, rarely for core entities.

5. In production¶

Migrations that lock. The list above (rewrites, full-scan constraints) is the checklist before every schema change on a hot table. Pattern: add nullable column -> backfill in batches -> add NOT VALID constraint -> VALIDATE -> SET NOT NULL via check constraint. Always with lock_timeout set and a retry.
Soft-delete leaks. One query without deleted_at IS NULL and a deleted customer appears in a report. Expose a view (customers_live) and grant the application access to the view, not the table.
Audit tables grow forever. Partition by month from day one; archive partitions to cheap storage; keep an index on (application_id, at) only.
Natural keys change. The country re-issues national IDs; a branch is merged. Because the PK is the surrogate, this is one UPDATE customers SET national_id = ... instead of a cascade through five tables, and the audit table still points at the same id.
Record-to-relational mapping artefacts. Packed records often held "spare" fields, bit flags, and fixed-width codes padded with spaces. Decide per field: drop, normalise into a lookup, or keep in a legacy jsonb column with a sunset date. Trim padding during migration and add a CHECK (code = trim(code)) so it cannot come back.
Time zone incidents: month-end batch runs at 00:00 UTC = 05:30 IST and includes the first five and a half hours of the next day. Compute business boundaries in the branch's zone.

6. Try it¶

(easy) Load code/m09_schema.sql and code/m09_seed.sql. Run the "FK without index" query from 2.4. Done when it returns no rows (every FK is indexed) and you can say why each index exists.
(easy) Try to insert an application with status = 'SUBMITTED' and no submitted_at, a negative amount, and an application number in the wrong format. Done when you can name the constraint in each error message.
(medium) Take this denormalised Berkeley DB style record and design its 3NF tables: {loan_no, cust_name, cust_national_id, cust_phone1, cust_phone2, branch_code, branch_name, product_code, product_rate, amount, status_char, officer_code, officer_name}. Write the DDL with keys, FKs and constraints. Done when a reviewer (or you, tomorrow) can find each original field exactly once and every FK has an index.
(medium) Implement a per-year gapless application_no generator as a function using a counter table with FOR UPDATE, and show from two sessions that it serialises correctly and never issues duplicates. Done when 2 x 1000 concurrent calls produce 2000 distinct numbers.
(hard) Convert application_audit into a range-partitioned table by month with 24 partitions and a default partition, migrate the existing rows, and show with EXPLAIN that a query with at BETWEEN ... prunes to one partition. Done when the plan lists one partition and DROP TABLE application_audit_2024_01 removes a month in milliseconds.

7. Interview questions¶

Q: Explain 3NF without jargon. A: Each fact is stored once, in the table whose key identifies it. No repeating groups in a row, no column that depends on only part of a composite key, and no column that depends on another non-key column. The payoff is no update anomalies; the cost is joins, which you denormalise deliberately and document when reads demand it.

Q: Surrogate or natural primary key? A: Surrogate bigint identity as the PK for stability and small FKs, plus a UNIQUE NOT NULL constraint on the natural key so it is enforced and indexed. The login bug I fixed was a missing unique constraint on the natural key, not a wrong choice of primary key. Pure link tables use a composite natural PK; stable short codes such as branch codes can be natural PKs by exception.

Q: Why index foreign key columns? A: Deleting or updating a parent row makes the database scan the child table for references; without an index that is a sequential scan under lock per parent delete. Joins through the FK also need it. PostgreSQL does not create the index automatically.

Q: Enum type or lookup table? A: Enums for tiny fixed sets that are part of the code; they are compact but cannot carry attributes and are awkward to change. Lookup tables for anything the business extends or that needs a display name, ordering or permissions, such as workflow states, where adding a state is an INSERT and the procedure reads the graph from the table.

Q: How do you model soft deletes and audit? A: A nullable deleted_at column, partial unique indexes that exclude deleted rows, and a live view for the application. Audit as an insert-only event table with from/to state, actor and transaction timestamp, written in the same transaction as the change, with UPDATE and DELETE revoked, and partitioned by month because it only grows.

Q: timestamp or timestamptz? A: Always timestamptz for instants: it stores UTC and renders in the session zone. Plain timestamp has no instant attached and silently differs between servers in different zones. Use date for calendar concepts, store IANA zone names when you need local rendering, and compute business-day boundaries in the business zone.

Q: Which ALTER TABLE operations are dangerous on a large hot table? A: Anything that rewrites (volatile defaults, most type changes) or scans under a strong lock (adding a validated FK or CHECK, SET NOT NULL). Safe patterns: add nullable columns, backfill in batches, add constraints NOT VALID then VALIDATE, create indexes CONCURRENTLY, and always set lock_timeout before DDL.

8. Recap¶

3NF: one fact, one place; denormalise deliberately and document the synchronisation.
Surrogate bigint PK + UNIQUE natural key; composite PK only for link tables.
Constraints are enforced documentation: NOT NULL, CHECK, UNIQUE (partial for soft delete), FK, EXCLUDE.
Index every FK column; PostgreSQL will not do it for you.
Lookup tables over enums for extensible sets; the workflow graph is data.
Soft delete with a live view; audit as insert-only, partitioned, written in the same transaction.
timestamptz for instants, date for dates, IANA zones, now() for transaction time.
Types: bigint, numeric for money, text with CHECK, boolean, jsonb sparingly; align columns.
FKs are system triggers with FOR KEY SHARE; sequences have gaps; know which ALTERs rewrite.
Record-to-relational mapping is normalisation of packed records with a kept reverse mapping.