relational-data-modeling

Relational Data Modeling

The decisions a relational schema forces, and how to settle them so the database enforces what it can and the application is left with only what it must. Every rule names the wrong default it corrects; there is no rule for what a capable model already gets right.

Examples are PostgreSQL 18, and every DDL statement in this skill was executed against 18.4 — including the failure cases, to confirm the constraints reject what they claim to reject. Roughly a third of the rules depend on mechanisms MySQL and SQLite do not have (EXCLUDE, partial unique indexes, range types, WITHOUT OVERLAPS, deferrable constraints, NOT VALID); those rules say so. The judgment rules transfer to any relational engine.

When to Apply

Use this skill when:

• Writing or reviewing CREATE TABLE / ALTER TABLE, a migration, or an ORM schema definition (Prisma, Drizzle, Django models, ActiveRecord, Ecto, SQLAlchemy)
• Designing an entity-relationship model, or naming what a row is — the point where key choices become expensive to reverse
• The user says "should this be one table or two", "how do I model many-to-many", "the schema allows bad data", "we need history", "we're adding multi-tenancy", or "can the database enforce this"
• A bug turns out to be a schema that permitted the bad state — duplicates that a unique constraint should have caught, orphans a foreign key should have blocked, overlapping bookings, two rows flagged as default
• Adding constraints to a table that already has rows and traffic
• Reviewing a schema generated by an ORM or a scaffolding tool, which is where polymorphic associations, reflexive surrogate keys, and blanket soft-delete flags arrive from

This skill is NOT for:

• Query tuning, execution plans, or choosing indexes for a read path — this covers only the indexes that constraints and foreign keys require
• Connection pooling, replication, or operational tuning
• Non-relational stores, where the trade-offs it argues from do not hold