Structuring data.
WHAT TO ESTABLISH
What entities exist How they relate What must be unique What must always be true
WHAT TO ENFORCE IN THE DATABASE
Constraints: uniqueness, foreign keys, not-null, checks.
WHY
They hold even when application code has a bug.
WHAT TO NORMALISE
Data, initially, to avoid duplication and inconsistency.
WHEN TO DENORMALISE
When measurement shows a genuine read problem.
WHAT TO INDEX
Columns used in filtering, joining and sorting.
WHAT TO BE CAREFUL WITH
Indexes on everything, which slow writes Indexes never used Missing indexes on foreign keys
WHAT TO USE FOR MONEY
Exact decimal types.
WHAT TO STORE TIMESTAMPS IN
A consistent time zone, ideally universal time.
WHY
Mixed time zones produce errors nobody diagnoses quickly.
WHAT TO PLAN
Migrations, as versioned code, reversible where possible.
WHAT TO BE CAREFUL WITH
Migrations locking large tables Changes that cannot be applied without downtime
WHAT TO TEST
Migrations against a copy of production-scale data.
