jhonatan-oliveiradev Designing Relational Data Models
Core principle
Model business invariants before tables. A durable relational model makes invalid states difficult to store, keeps relationships explicit, and leaves performance decisions tied to real access patterns rather than guesses.
Workflow
- Write down the important entities, stable identities, ownership rules, and lifecycle transitions in domain language.
- Define relationship cardinality and deletion behavior explicitly. Distinguish ownership from simple association.
- Choose columns from facts the system must preserve. Prefer normalized relational structure for stable, queryable facts; use JSON only when the shape is genuinely variable or opaque to the database.
- Encode invariants close to the data with
NOT NULL,UNIQUE, foreign keys, andCHECKconstraints when PostgreSQL can enforce them safely. - List the concrete reads and writes the product must support before choosing indexes. Include filters, joins, ordering, pagination, and expected selectivity.
- Add the smallest useful indexes for those access patterns. Remember that every index adds write, storage, and maintenance cost.
- Walk realistic create, update, delete, and concurrency scenarios through the model before implementation.
Decision rules
- Prefer stable surrogate identifiers when business identifiers can change independently of identity.
- Do not duplicate a fact into several tables without an explicit consistency strategy.
- Do not add nullable columns by default. Null should have a defined domain meaning.
- Do not use denormalization as a substitute for measuring a query problem.
- Composite index order must follow actual predicate and ordering needs; do not treat multicolumn indexes as generic coverage.
Acceptance checks
- Invalid domain states are rejected by application logic and, where appropriate, database constraints.
- Ownership, cardinality, nullability, and deletion semantics are documented.
- Every proposed index maps to at least one concrete access pattern.
- Common reads can be expressed without ambiguous ownership or hidden synchronization rules.
References
- PostgreSQL constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
- PostgreSQL indexes: https://www.postgresql.org/docs/current/indexes.html
- PostgreSQL multicolumn indexes: https://www.postgresql.org/docs/current/indexes-multicolumn.html