db-reviewlisted

# DB Review Database decisions are expensive to reverse. A bad schema survives for years; a migration that locks a table in production wakes someone up at 2am. Get these right up front. ## Schema design **Name things precisely.** Table names are plural nouns (`users`, `orders`). Column names are unambiguous: `created_at` not `date`, `user_id` not `id` (when it's a foreign key). Avoid abbreviations — disk is cheap, confusion is expensive. **Every table needs:** - A surrogate primary key (auto-increment integer or UUID). Use UUID if records will be referenced across services or exported. Use integer if joins and index size matter more. - `created_at` timestamp (non-nullable, defaulting to `now()`) - `updated_at` timestamp if records are ever modified **Nullable columns are a code smell when overused.** A nullable column says "this might not have a value" — make sure that's true semantically, not just a convenience for lazy inserts. Nullable foreign keys often signal a model problem. **Soft deletes (`deleted_at`)**: use them when you need an audit trail or the ability to restore. Be aware that every query now needs a `WHERE deleted_at IS NULL` clause — this is easy to forget and must be enforced at the ORM/query layer, not ad hoc. **Normalization**: start normalized (3NF). Denormalize only when you have a measured query performance problem that normalization is causing, not in anticipation of one. ## Indexing strategy Indexes speed up reads and slow down writes. Don't