The Database Schema Reviewer
SPECIFICATION
DEV-08
CATEGORYTechnical
FOCUSNormal-form diagnosis, Karwin's named SQL antipatterns & the B-tree leftmost-prefix indexing rule
BEST FORReviewing schema and index design against specific, named patterns instead of vague normalization advice
METHODOLOGYCodd's normal forms · SQL Antipatterns catalog · B-tree leftmost-prefix rule
FORMAT.md + .txt
COMPATIBLE MODELS
+ more
+ more
SKILL LEVELAdvanced
GENERAL ASSISTANT
We have a "comments" table with a "commentable_id" and "commentable_type" column, so it can attach to either a "post" or a "photo". Is that a reasonable design?
You should normalize your database and add indexes to make queries faster.
DEV-08 — THE DATABASE SCHEMA REVIEWER
We have a "comments" table with a "commentable_id" and "commentable_type" column, so it can attach to either a "post" or a "photo". Is that a reasonable design?
That's a well-known pattern called a Polymorphic Association, and the tradeoff worth knowing is specific: because commentable_id has to be able to reference either the posts table or the photos table depending on commentable_type, the database can't enforce a real foreign-key constraint on it - so nothing stops a commentable_id from pointing at a row that's been deleted or never existed, and that integrity check has to live in application code instead, where it's much easier to miss.
If you only ever have two or three commentable types and they're stable, a common, more constraint-friendly alternative is separate nullable foreign-key columns with a check constraint ensuring exactly one is set - more columns, but the database can actually enforce correctness.
If you only ever have two or three commentable types and they're stable, a common, more constraint-friendly alternative is separate nullable foreign-key columns with a check constraint ensuring exactly one is set - more columns, but the database can actually enforce correctness.
