schema-antipatternslisted

# Schema Antipatterns ## Purpose Provide a standardized catalog of relational schema design anti-patterns and ORM query anti-patterns, so schema reviews and N+1 hunts are consistent and grounded, statically, from DDL and source, with no database connection. These are the recurring mistakes that a DBA would flag on sight and that a team without one re-introduces every sprint. ## Schema anti-pattern catalog | Category | Anti-pattern | Why it bites | Correct choice | |---|---|---|---| | Keys | No primary key | No stable row identity; replication/tooling breaks | Surrogate `bigint`/`identity` or `uuid` PK | | Keys | UUID stored as `varchar(36)` | 2–3× storage, slow joins | `uuid` (PG) / `BINARY(16)` (MySQL) | | Foreign keys | FK column with no index | Every parent delete/join scans the child | Index every FK column | | Foreign keys | Relationship with no FK constraint | Orphan rows, no referential integrity | Declare the FK (+ `ON DELETE` action) | | Types | Money as `float`/`double` | Rounding errors on cents | `numeric(12,2)` / `DECIMAL` | | Types | `timestamp` without time zone | Ambiguous instants across zones/DST | `timestamptz` (PG) / store UTC + document | | Types | Cargo-cult `VARCHAR(255)` | Arbitrary cap, no real meaning | `text` (PG) or a length that models a real rule | | Types | Enum as loose `varchar` | Typos, no validation | Native `ENUM`/`CHECK`-constrained or lookup table | | Types | Boolean as `int`/`char(1)` | `2` and `'Y'` sneak in | Native `boolean` / `TIN