Schema Design and Data Modeling

What we're doing: Show a 3NF violation causing an update anomaly (two rows disagreeing about the same fact), then the normalized fix that makes the anomaly structurally impossible.

CREATE TABLE orders_denorm (id SERIAL PRIMARY KEY, zip TEXT, city TEXT);
INSERT INTO orders_denorm (zip, city) VALUES ('10001', 'New York'), ('10001', 'New York');

UPDATE orders_denorm SET city = 'NYC' WHERE id = 1;
-- only order 1 updated -- order 2 still says 'New York' for the SAME zip code

SELECT DISTINCT zip, city FROM orders_denorm;
-- one zip code, two different city spellings -- an update anomaly, structurally possible here

2

Both orders share zip 10001, and both should always agree on the city that zip maps to.

4

Updating just one row's city leaves the schema in an inconsistent state — nothing prevents zip and city from disagreeing across rows.

7–8

The same zip code now maps to two different city spellings — a direct consequence of storing city redundantly instead of deriving it from zip via a lookup.

zip   | city
-------+----------
10001 | NYC
10001 | New York
(2 rows)

Why this works: Nothing in the orders_denorm schema enforces that every row with zip = '10001' agrees on city, because city is stored independently per row rather than derived from a single authoritative source — this is exactly the transitive dependency 3NF is defined to eliminate. Moving city into its own zip_codes table keyed by zip makes the inconsistency structurally impossible: there would be exactly one row for zip 10001, and every order referencing it automatically sees the same, single value.

What we're doing: Model two relationships from the same table that differ in optionality but not cardinality, and confirm each behaves as intended.

CREATE TABLE customers (id SERIAL PRIMARY KEY);
CREATE TABLE promo_codes (id SERIAL PRIMARY KEY);
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),    -- required: every order needs one
    promo_code_id INT REFERENCES promo_codes(id)           -- optional: most orders have none
);

INSERT INTO customers DEFAULT VALUES;
INSERT INTO orders (customer_id, promo_code_id) VALUES (1, NULL);
-- succeeds: promo_code_id is optional

INSERT INTO orders (customer_id) VALUES (NULL);
-- fails: customer_id is required

4

NOT NULL encodes "required" — both relationships are one-to-many from orders' perspective, but only this one is mandatory.

5

No NOT NULL here — the same cardinality shape (many orders, one promo code), but legitimately optional.

9–10

An order with no promo code is valid — the relationship is genuinely optional.

INSERT 0 1

ERROR:  null value in column "customer_id" of relation "orders" violates not-null constraint

Why this works: Both customer_id and promo_code_id are foreign keys with identical cardinality (many orders can point at one customer, or one promo code) — the only schema-level difference is NOT NULL, which is exactly where optionality lives. This is why cardinality and optionality need to be reasoned about as two separate questions: getting the cardinality right (which table holds the foreign key) does not automatically get the optionality right (whether NOT NULL belongs on it).

What we're doing: Show why using a natural key (email) directly as a foreign key target creates a problem when that email changes, compared to referencing a stable surrogate key.

-- natural key as primary key -- fragile
CREATE TABLE users_natural (email TEXT PRIMARY KEY);
CREATE TABLE orders_natural (id SERIAL PRIMARY KEY, user_email TEXT REFERENCES users_natural(email));

INSERT INTO users_natural VALUES ('ada@example.com');
INSERT INTO orders_natural (user_email) VALUES ('ada@example.com');

UPDATE users_natural SET email = 'ada.lovelace@example.com' WHERE email = 'ada@example.com';
-- fails by default: orders_natural still references the OLD email

2

email is both the identity and the changeable business fact — the exact conflict a surrogate key avoids.

8–9

Changing the email breaks referential integrity, since orders_natural still points at the old value — without ON UPDATE CASCADE, this UPDATE is rejected outright.

ERROR:  update or delete on table "users_natural" violates foreign key constraint "orders_natural_user_email_fkey" on table "orders_natural"
DETAIL:  Key (email)=(ada@example.com) is still referenced from table "orders_natural".

Why this works: The natural key (email) is doing two jobs at once — identifying the row AND representing a real, mutable fact about the user — and those two jobs conflict the moment the fact changes. A surrogate id column would let email change freely without touching any foreign key at all, because nothing else in the schema depends on the email's specific value; the surrogate id never had any business meaning to begin with, so there is nothing about it that would ever legitimately need to change.

What we're doing: Compare the actual byte size of a bigint identifier against a UUID for the same number of rows, making the storage trade-off concrete.

SELECT pg_column_size(9223372036854775807::bigint) AS bigint_bytes,
       pg_column_size(gen_random_uuid()) AS uuid_bytes;

1

A bigint, regardless of its actual value, always occupies 8 bytes.

2

A UUID always occupies 16 bytes — exactly double, before even counting any index overhead from non-sequential insertion order.

bigint_bytes | uuid_bytes
--------------+-----------
            8 |         16

Why this works: The size difference is fixed and applies to every row and every index entry referencing that key, which compounds across a large table and every foreign key column that stores a copy of it — this is the concrete cost side of the trade-off. The benefit side (client-side generation, no revealed ordering, safe merging across systems without collision) does not show up in a byte count, which is exactly why the decision needs to weigh the actual requirements rather than defaulting to whichever looks smaller.

What we're doing: Confirm created_at sets itself automatically on INSERT, while updated_at requires a trigger to stay current on UPDATE.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_set_updated_at BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO posts (title) VALUES ('Hello world');
SELECT pg_sleep(1);
UPDATE posts SET title = 'Hello, world!' WHERE id = 1;

SELECT created_at = updated_at AS unchanged_since_insert FROM posts WHERE id = 1;

4

created_at defaults to now() at INSERT time and is never touched again.

8–13

The trigger is what actually keeps updated_at current — without it, an UPDATE would leave updated_at at its original INSERT-time value forever.

16

A 1-second pause makes the before/after difference in updated_at unambiguous in the output.

unchanged_since_insert
------------------------
f

Why this works: created_at and updated_at start out identical at INSERT time, since both default to now() in the same statement — the trigger is the only reason they diverge after the UPDATE, because it explicitly overwrites NEW.updated_at with the current time on every update. Without that trigger (or equivalent application-level discipline), updated_at would still read false as "unchanged," giving a misleading impression that the row was never modified after creation.

What we're doing: Show a plain UNIQUE constraint blocking a legitimate re-signup with a deleted user's email, then fix it with a partial unique index.

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, deleted_at TIMESTAMPTZ);

INSERT INTO users (email) VALUES ('ada@example.com');
UPDATE users SET deleted_at = now() WHERE email = 'ada@example.com';
-- soft-deleted, but the UNIQUE constraint doesn't know or care

INSERT INTO users (email) VALUES ('ada@example.com');
-- ERROR: still blocked -- plain UNIQUE applies regardless of deleted_at

-- fix: drop the plain UNIQUE, add a partial one instead
ALTER TABLE users DROP CONSTRAINT users_email_key;
CREATE UNIQUE INDEX users_email_active_key ON users (email) WHERE deleted_at IS NULL;
INSERT INTO users (email) VALUES ('ada@example.com');
-- succeeds -- the old row is soft-deleted, so it's excluded from this uniqueness check

3–5

Soft-deleting a row sets deleted_at, but a plain UNIQUE constraint has no awareness of that column at all.

7–8

Re-signing up with the same email fails, even though the original account is conceptually gone.

11–12

The partial index only enforces uniqueness among rows where deleted_at IS NULL — the soft-deleted row no longer counts.

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.

INSERT 0 1

Why this works: A plain UNIQUE index has no concept of "this row doesn't count anymore" — it enforces uniqueness across literally every row in the table regardless of any other column's value, including deleted_at. A partial index with a WHERE clause changes what the index actually covers: CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL builds an index (and therefore a uniqueness guarantee) over only the rows matching that condition, so a soft-deleted row's email genuinely stops counting toward the constraint the moment deleted_at is set.

What we're doing: Add a new nullable column to an existing populated table, confirming it is a cheap, ordinary operation rather than something requiring speculative up-front design.

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO customers (name) VALUES ('Ada'), ('Grace'), ('Linus');

-- months later, a real business need appears: track phone numbers
ALTER TABLE customers ADD COLUMN phone TEXT;

SELECT * FROM customers;

2

Three existing rows, created with no anticipation of a phone column.

5

Adding a nullable column to a populated table is a fast, standard schema change — existing rows simply get NULL for the new column.

id | name  | phone
---+-------+-------
 1 | Ada   |
 2 | Grace |
 3 | Linus |
(3 rows)

Why this works: PostgreSQL adding a nullable column with no default is a fast, metadata-only operation — it does not need to rewrite every existing row, since a NULL for a new column requires no physical change to rows that predate the column. This is exactly why "we might need a phone field later" does not justify building a speculative generic-attributes system up front: the actual cost of adding the real, typed column later, when the need is confirmed, turns out to be low.