database-schema-design

name	database-schema-design
description	Design relational or document schemas from access patterns, cardinality, and lifecycle. Use when modeling entities, choosing embed vs normalize, or shaping schema boundaries before implementation.
metadata	{"triggers":{"files":["**/*.entity.ts","prisma/schema.prisma","**/*.json"],"keywords":["schema","data model","cardinality","embed","normalize"]}}

Database Schema Design

Priority: P0 (CRITICAL)

Start from reads, writes, and ownership. Schema follows access patterns, not vice versa.

Rules

• Model one business concept per table/collection boundary.
• Choose embed vs reference or normalize vs denormalize from cardinality, update frequency, and read locality.
• Encode uniqueness, nullability, and foreign-key or ownership rules explicitly.
• Prefer additive evolution over destructive redesigns.

Verify

• Hot reads are supported without avoidable joins or fan-out.
• Cardinality and lifecycle were written down for major relationships.
• Constraints or validation rules exist for business invariants.
• IDs, timestamps, and soft-delete semantics are consistent.

Anti-Patterns

• No schema from ORM defaults: model business access patterns first.
• No many-to-many without owner rules: define source of truth and cleanup behavior.
• No nullable drift: nullable fields need lifecycle meaning.

References

• Framework Map
• Normalization Tradeoffs