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.