data-modeling

# Data Modeling ## Purpose Design a schema that supports the queries you need and the changes you will want. Schema mistakes are the most expensive category of technical debt because the data outlives every application that touched it. ## When to Use - Designing a new schema or a significant new table. - Adding multi-tenancy to an existing model. - Modeling history, versioning, or audit requirements. - Reviewing a schema before it becomes hard to change. ## Capabilities - Normalization, and deliberate denormalization. - Key selection: natural, surrogate, UUID, ULID. - Temporal modeling: valid time, transaction time, event history. - Multi-tenancy strategies and their isolation guarantees. - Constraint design: the invariants the database should enforce. ## Inputs - The domain entities and the relationships between them. - The queries the schema must serve, and their frequency. - Retention, audit, and compliance requirements. ## Outputs - A schema with enforced constraints and appropriate keys. - A documented tenancy and soft-delete strategy. - A migration path from the current schema, if there is one. ## Workflow 1. **Normalize first** — Third normal form as the default. Denormalize only where a measured query cost justifies it, and record the decision. 2. **Choose keys deliberately** — A surrogate key (UUID/ULID) is stable and safe to expose. A natural key is meaningful and changes when the business changes its mind. Prefer surrogates for identity, and enforce nat...