Practical schema decisions.
WHAT EVERY TABLE SHOULD HAVE
A primary key Created and updated timestamps Types matching the data, not defaults
WHAT TO CHOOSE FOR PRIMARY KEYS
An auto-incrementing integer, in most cases.
WHAT THAT PROVIDES
Small indexes, and ordered inserts.
WHAT IDENTIFIERS EXPOSED PUBLICLY RISK
Revealing volume, and permitting enumeration.
WHAT TO DO ABOUT IT
A separate public identifier that is not sequential.
WHY NOT MAKE THAT THE PRIMARY KEY
Random keys scatter inserts and enlarge every index referencing them.
WHAT TO USE FOR STATUS COLUMNS
A constrained set of values, enforced.
WHY NOT FREE TEXT
It accumulates variations nobody intended.
WHAT TO DO ABOUT OPTIONAL DATA
Allow it to be absent, deliberately, rather than storing empty strings.
WHY THAT DISTINCTION MATTERS
Absent and empty mean different things, and mixing them breaks queries.
WHAT TO AVOID
Very wide tables with rarely used columns Storing several values in one column Columns named after their type
WHAT TO DO ABOUT SEVERAL VALUES
A separate table.
WHAT TO PLAN FOR
Soft deletion, if records must remain recoverable.
WHAT THAT REQUIRES
Every query excluding deleted rows, and an index accounting for it.
