Data Modeling Best Practices

Q: What is the minimum integrity bar for a new table?

Primary key, created_at timestamptz, ownership comment, and constraints for every business rule you can name in one sentence.

Q: When should I denormalize?

After normalized design is live and pg_stat_statements proves join cost blocks SLOs. Document what must stay in sync and how you detect drift.

Q: What is the top modeling mistake in SaaS?

Forgetting tenant_id on child tables or indexes that lead with tenant_id - causes cross-tenant leaks and slow queries.

Model the domain, not the current UI screen. Use this list during schema reviews, migration PRs, and ADR updates.

How to Use This List

Walk it top-to-bottom for greenfield tables; use single sections for targeted reviews.
Record violations as migration tickets with an owner and deadline.
Revisit quarterly or after any data-corruption incident.
Pair with EXPLAIN and integrity queries - modeling mistakes show up as orphans and fan-out joins.

A - Domain Fidelity

Name tables after domain nouns, not screen labels. checkout_summary_row becomes orders + line_items when the UI changes.
Encode invariants in the database. Use NOT NULL, CHECK, UNIQUE, and FK constraints - not only application validation.
Document table ownership in COMMENT ON. Every production table has a single writer service or team name.
Prefer timestamptz for instants. Store UTC; convert at display. Avoid timestamp without time zone for user events.
Use surrogate bigint identity keys for OLTP. Natural keys belong in UNIQUE constraints when business-guaranteed.

B - Relationships and Cardinality

Resolve many-to-many with junction tables. Never duplicate tag columns (tag1, tag2) on a fact table.
Index every foreign key column. Unindexed FKs cause slow joins and costly cascades on delete.
Make optionality explicit. Required parent: NOT NULL FK; optional: nullable FK plus LEFT JOIN in queries.
Choose ON DELETE deliberately. CASCADE, RESTRICT, and SET NULL should match business rules, not defaults.
Avoid cross-context FKs unless teams deploy together. Prefer logical UUID references between bounded contexts.

C - Physical PostgreSQL Design

Map bounded contexts to schemas. Keep public from becoming a shared junk drawer.
Grant least privilege per app role. Services should not UPDATE tables they do not own.
Plan schema evolution with expand-contract. Add columns and compat views before dropping old shapes.
Snapshot prices and labels on transactional rows. order_items.unit_price must not change when catalog price changes.
Lint naming conventions in CI. Consistent snake_case, plural tables, and _id suffixes speed reviews.

D - Operations and Longevity

Record ADRs for database topology. Single shared DB vs per-service DB decisions need a review date.
Test migrations on a copy of production stats. Cardinality estimates affect index and partition choices.
Measure before denormalizing. Prove read pain with pg_stat_statements; document refresh invariants if you denorm.
Run orphan-detection queries after backfills. LEFT JOIN ... WHERE parent IS NULL should return zero rows.
Deprecate compatibility views on a calendar. Views without end dates become permanent cruft.

FAQs

Why model the domain instead of the UI?

Screens change weekly; domain rules (an order has line items, a user has one email) last years. UI-shaped tables require painful migrations when navigation changes.

What is the minimum integrity bar for a new table?

Primary key, created_at timestamptz, ownership comment, and constraints for every business rule you can name in one sentence.

When should I denormalize?

After normalized design is live and pg_stat_statements proves join cost blocks SLOs. Document what must stay in sync and how you detect drift.

How do I enforce ownership with multiple services?

Separate schemas, separate DB roles, code review checklist, and optionally REVOKE cross-schema INSERT/UPDATE from app roles.

Should every column have a comment?

Comment tables and non-obvious columns (status enums, soft-delete flags, currency assumptions). Skip id-level noise.

What is the top modeling mistake in SaaS?

Forgetting tenant_id on child tables or indexes that lead with tenant_id - causes cross-tenant leaks and slow queries.

How do I review a PR that adds a JSONB column?

Ask: which fields need FK integrity, indexing, and CHECK constraints? Unstructured JSON is a staging area, not a destination schema.

When are PostgreSQL ENUM types acceptable?

Rarely-changing closed sets with slow release cadence. Prefer text + CHECK when product adds values frequently.

How do views fit in modeling best practices?

Use versioned compat views for API stability during migrations. Do not let apps query ad-hoc views instead of owned tables without documentation.

What should I bring to an architecture review?

ERD or table list, ownership matrix, migration expand-contract plan, and one query that proves the hot path index exists.

Data Modeling Basics - introductory examples
Entity-Relationship Modeling - cardinality reference
Domain-Driven Schema Boundaries - schema boundaries
Schema Evolution Strategy - safe change over time
Normalization Basics - anomaly prevention

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Data Modeling Best Practices

Model the domain, not the current UI screen. Use this list during schema reviews, migration PRs, and ADR updates.

How to Use This List

Walk it top-to-bottom for greenfield tables; use single sections for targeted reviews.
Record violations as migration tickets with an owner and deadline.
Revisit quarterly or after any data-corruption incident.
Pair with EXPLAIN and integrity queries - modeling mistakes show up as orphans and fan-out joins.

A - Domain Fidelity

Name tables after domain nouns, not screen labels. checkout_summary_row becomes orders + line_items when the UI changes.
Encode invariants in the database. Use NOT NULL, CHECK, UNIQUE, and FK constraints - not only application validation.
Document table ownership in COMMENT ON. Every production table has a single writer service or team name.
Prefer timestamptz for instants. Store UTC; convert at display. Avoid timestamp without time zone for user events.
Use surrogate bigint identity keys for OLTP. Natural keys belong in UNIQUE constraints when business-guaranteed.

B - Relationships and Cardinality

Resolve many-to-many with junction tables. Never duplicate tag columns (tag1, tag2) on a fact table.
Index every foreign key column. Unindexed FKs cause slow joins and costly cascades on delete.
Make optionality explicit. Required parent: NOT NULL FK; optional: nullable FK plus LEFT JOIN in queries.
Choose ON DELETE deliberately. CASCADE, RESTRICT, and SET NULL should match business rules, not defaults.
Avoid cross-context FKs unless teams deploy together. Prefer logical UUID references between bounded contexts.

C - Physical PostgreSQL Design

Map bounded contexts to schemas. Keep public from becoming a shared junk drawer.
Grant least privilege per app role. Services should not UPDATE tables they do not own.
Plan schema evolution with expand-contract. Add columns and compat views before dropping old shapes.
Snapshot prices and labels on transactional rows. order_items.unit_price must not change when catalog price changes.
Lint naming conventions in CI. Consistent snake_case, plural tables, and _id suffixes speed reviews.

D - Operations and Longevity

Record ADRs for database topology. Single shared DB vs per-service DB decisions need a review date.
Test migrations on a copy of production stats. Cardinality estimates affect index and partition choices.
Measure before denormalizing. Prove read pain with pg_stat_statements; document refresh invariants if you denorm.
Run orphan-detection queries after backfills. LEFT JOIN ... WHERE parent IS NULL should return zero rows.
Deprecate compatibility views on a calendar. Views without end dates become permanent cruft.

FAQs

Why model the domain instead of the UI?

Screens change weekly; domain rules (an order has line items, a user has one email) last years. UI-shaped tables require painful migrations when navigation changes.

What is the minimum integrity bar for a new table?

Primary key, created_at timestamptz, ownership comment, and constraints for every business rule you can name in one sentence.

When should I denormalize?

After normalized design is live and pg_stat_statements proves join cost blocks SLOs. Document what must stay in sync and how you detect drift.

How do I enforce ownership with multiple services?

Separate schemas, separate DB roles, code review checklist, and optionally REVOKE cross-schema INSERT/UPDATE from app roles.

Should every column have a comment?

Comment tables and non-obvious columns (status enums, soft-delete flags, currency assumptions). Skip id-level noise.

What is the top modeling mistake in SaaS?

Forgetting tenant_id on child tables or indexes that lead with tenant_id - causes cross-tenant leaks and slow queries.

How do I review a PR that adds a JSONB column?

Ask: which fields need FK integrity, indexing, and CHECK constraints? Unstructured JSON is a staging area, not a destination schema.

When are PostgreSQL ENUM types acceptable?

Rarely-changing closed sets with slow release cadence. Prefer text + CHECK when product adds values frequently.

How do views fit in modeling best practices?

Use versioned compat views for API stability during migrations. Do not let apps query ad-hoc views instead of owned tables without documentation.

What should I bring to an architecture review?

ERD or table list, ownership matrix, migration expand-contract plan, and one query that proves the hot path index exists.

Data Modeling Basics - introductory examples
Entity-Relationship Modeling - cardinality reference
Domain-Driven Schema Boundaries - schema boundaries
Schema Evolution Strategy - safe change over time
Normalization Basics - anomaly prevention

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Data Modeling Best Practices

Search Documentation

Data Modeling Best Practices