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 / TINYINT(1) with CHECK
Constraints	Nullable-everything	NULL means "unknown" everywhere; buggy logic	NOT NULL on required columns
Constraints	Missing UNIQUE/CHECK	Duplicate/invalid data	Add the business-rule constraint
Indexing	Over-indexing / duplicate index	Write amplification, wasted storage	One index per access pattern; drop prefixes
Indexing	Index on low-selectivity boolean	Rarely used, still maintained	Partial index or none
Integrity	Soft-delete without partial unique	UNIQUE(email) blocks re-signup after delete	UNIQUE ... WHERE deleted_at IS NULL

Severity guidance

• Critical: data-loss/integrity, money as float, missing PK, missing FK on a real relationship.
• High: correctness/perf, unindexed FK, missing NOT NULL, wrong time type.
• Medium: maintainability, VARCHAR(255), duplicate index, enum-as-string.
• Low: style/naming, inconsistent casing, non-standard plurals.

ORM N+1 in one line

An N+1 is: a query returns N rows, then code touches a lazy relation once per row → 1 + N queries. The fix is always to eager-load or batch the relation before the loop. The exact call differs per ORM, see the reference.

Additional Resources

Reference Files

• references/schema-checklist.md: the full review checklist: types, keys, constraints, indexing FKs, normalization vs denormalization trade-offs, timestamps/time zones, soft-delete, and naming conventions.
• references/orm-n1-patterns.md: per-ORM: the exact code shape that causes an N+1 and the exact eager-loading fix for Django, SQLAlchemy, ActiveRecord, Sequelize, Prisma, TypeORM, Hibernate/JPA, GORM, and Entity Framework.