Schema Design

Schema is the most expensive thing in an application to get wrong, because every piece of code and every row already written depends on it.

Normalisation, practically. First normal form means atomic values — no comma-separated lists in a column, no repeating groups. Second means no non-key column depends on only part of a composite key. Third means no non-key column depends on another non-key column: storing customer_id, customer_name, and customer_city on an order puts the customer's details in every order they ever place, so a change of city means updating a million rows or, more likely, updating some of them.

The working rule: normalise to third normal form, then denormalise deliberately and knowingly. Denormalisation is a legitimate performance decision and it is also a consistency obligation you have chosen to take on.

Let the database enforce what must be true. A foreign key, a unique constraint, a check constraint, and NOT NULL are enforced for every writer, including the migration script someone runs by hand at 11pm and the bug you have not written yet. Application-level validation covers only the paths you remembered. Constraints are also documentation that cannot go stale.

Keys. A natural key is meaningful data — an email, an ISBN. A surrogate key is a generated identifier with no meaning. Prefer surrogates as primary keys, because natural keys change: people change email addresses, countries reissue codes, and a primary key that changes cascades everywhere. Keep the natural key as a unique constraint, which is what you actually wanted.

Nullable columns are a design decision. NULL means "unknown", and a column that is nullable because it was easier at the time forces every reader to handle a case that never legitimately occurs.

Model the relationship, not the screen. Schemas shaped around the first UI that needed them age badly. Ask what is true about the domain: does an order have one address or many, can a user belong to several organisations, is this relationship exclusive.