Schema Design

Schema mistakes are cheap to fix at a thousand rows and expensive at a billion. The decisions that matter are types, keys, nullability and where you stop normalising.

Types

• Use the narrowest type that fits the domain. Type width multiplies across every row and every index entry. An int where a bigint is unnecessary saves four bytes per row per index.
• Store time as an instant with a zone. timestamptz in PostgreSQL, TIMESTAMP in MySQL (which converts to UTC). timestamp without time zone silently reinterprets values as the session zone changes.
• Never store money as floating point. Use numeric/DECIMAL, or integer minor units. Binary floating point cannot represent 0.1, and the error compounds across a ledger.
• Constrain enumerations. A CHECK constraint or an enum type rejects the typo that a text column accepts forever.
• Use JSON for genuinely variable data only. A jsonb column holding attributes that vary per row is good design. A jsonb column holding the same twelve keys in every row is a table someone declined to define, and it costs you constraints, statistics and index quality.

Keys

Every table needs a primary key. Beyond correctness, several engines require one: logical replication cannot replicate updates without a replica identity, and Galera requires primary keys on replicated tables.

Nullability

Declare NOT NULL wherever the value is required. It is documentation the database enforces, it lets the planner reason better, and adding it later means validating every existing row.

Be deliberate about what NULL means: "unknown", "not applicable" and "empty" are three different things, and SQL's three-valued logic will surprise anyone who conflates them. WHERE status <> 'active' does not return rows where status is NULL.

Normalisation

Normalise by default; denormalise for a measured reason. Normalised data has exactly one place to update, so it cannot become internally inconsistent.

Legitimate reasons to denormalise:

• A join that is genuinely too expensive at your data volume, demonstrated with a query plan.
• Immutable historical facts: an order line must keep the price at the time of sale, not follow the product's current price. This is not denormalisation — it is correct modelling.
• Engines where joins are unavailable or prohibitively expensive, which is the normal case for wide-column and document stores.

Designing for change

Assume every table will be altered while in production. Two habits make that cheap:

• Prefer additive changes — new nullable columns, new tables — over changes to existing columns.
• Keep the write path narrow: fewer columns touched per statement means fewer migrations that need coordination with application deploys. See Zero-Downtime Migrations.