Best Practices in Data Modeling

Best Practices in Data Modeling

A "best practice" you can't justify is just a slogan. Every rule below reduces to one of two mechanisms: the database keeps redundant structures (indexes, denormalized copies) that speed reads but must be maintained on every write, and it enforces invariants (types, constraints, keys) at exactly one place so they can't be bypassed. Learn the mechanism and each rule stops being a rule you memorize and becomes a cost you can weigh.

1. Index for the reads you actually run — and pay for it on every write

An index is a separate data structure — almost always a B-tree the engine keeps sorted by the indexed column(s), with a pointer back to the row. A lookup on that column becomes an O(log n) tree descent instead of an O(n) full-table scan. That is the whole benefit, and it is large: on a 10-million-row table, a selective indexed lookup touches roughly log₂(10,000,000) ≈ 23 nodes instead of scanning 10,000,000 rows.

The cost is symmetric and unavoidable: because the index is a separate sorted structure, every INSERT, every DELETE, and every UPDATE that changes an indexed column must also insert, remove, or relocate the matching entry in that index — keeping it sorted, sometimes splitting a B-tree page, and writing more to the write-ahead log (WAL). A table with 6 indexes pays roughly 6× the index-maintenance work on every such write (plus the heap write itself).

Traced example. Table orders, 10M rows. Query WHERE customer_id = 42 returns ~50 rows.

• No index: full scan ≈ 10,000,000 row reads to find 50. Read: slow. Write of one new order: 1 heap write only.
• Add idx(customer_id): the read becomes ~23 B-tree node reads + 50 row fetches — from 10,000,000 rows examined down to roughly 70, several orders of magnitude fewer. But each INSERT now does 1 heap write plus 1 index-entry insert. Add two more indexes (status, created_at) and one insert becomes 1 heap + 3 index writes ≈ 4× the write work and more WAL, as the diagram shows.

Rule: index a column only if (a) a real query filters, joins, or sorts on it, and (b) the column is selective. Indexing a boolean is_active that is 90% true rarely helps — the planner estimates it will read most of the table and scans anyway, so you get the write cost with none of the read benefit. Confirm the index is used with EXPLAIN, and periodically drop indexes that pg_stat_user_indexes reports as never scanned. Never index a write-hot table "just in case."

2. Normalize by default; denormalize only against proof

Normalization means each fact lives in exactly one place (customer name in customers, referenced by id from orders). The mechanism that makes this safe: with one copy, there is no way for two rows to disagree, so the classic update anomaly — rename a customer and have some orders still show the old name — is structurally impossible. The cost is that reads which need the name must JOIN back to customers.

Denormalization copies the name into every order row. Reads get faster (single-row lookup, no join), but you have manufactured the update anomaly: rename the customer and every copy must be rewritten, or the copies drift (see the diagram — one missed write leaves "Acne" behind). You now own a consistency problem the normalized schema didn't have.

"Judiciously" has a concrete trigger. Denormalize only when all three hold: (1) a specific read query is proven hot — it sits at the top of pg_stat_statements; (2) re-joining is the measured bottleneck for that query, not a guess; and (3) the redundant copy can be kept correct automatically by a trigger, a materialized view, or a scheduled refresh. If you can't name the query and show the numbers, you are denormalizing prematurely.

The named trade-off: normalization = one source of truth, cheap correct writes, join-heavy reads; denormalization = duplicated data, fast reads, and a consistency burden where every write must fan out to the copies or they diverge. Related alternatives that avoid owning the copy: a covering/composite index (let the engine serve the read without a heap trip), a cache with an explicit TTL, or a read replica. Reach for those before you duplicate columns.

3. Right-size data types

The declared type is not cosmetic — it fixes on-disk width, index size, and comparison semantics. BIGINT is 8 bytes, INT 4; on a billion-row table that is ~4 GB of difference in the table and in every index over that column, which is ~4 GB more to cache and scan. Comparison correctness matters just as much: store money as DECIMAL/NUMERIC, never FLOAT — 0.1 + 0.2 ≠ 0.3 in binary floating point, so a total can be a cent off. Store timestamps as timestamptz, not a string, so range comparisons and sorting are correct rather than lexicographic. Rule: pick the smallest type that fits the real domain with headroom, and a type whose ordering matches how you'll query it.

4. Enforce constraints at the database, not only in the app

A NOT NULL, UNIQUE, CHECK, or FOREIGN KEY constraint is a single source of truth for an invariant, checked no matter which client writes. App-only validation is bypassed by the next service, a batch script, a manual psql session, or a bug in one code path — and once bad data lands, it is expensive to find and repair. The database enforces the rule for all writers, atomically, at the point of write. Rule: every invariant the data must always satisfy (a required field, a valid enum value, a real parent row, a unique email) belongs as a DB constraint; treat app-side checks as a fast-feedback convenience layer on top, not the guarantee.

5. Prefer a surrogate key, but keep the natural key as a constraint

A surrogate key (an auto-generated BIGINT or UUID with no business meaning) is stable: it never changes, so foreign keys pointing at it never need cascading rewrites when a real-world value (email, ISBN, tax id) changes. Natural keys carry meaning but mutate, and a mutating primary key forces updates across every referencing row and index. Rule: use a surrogate as the primary key for stability, and still put a UNIQUE constraint on the natural key so the real-world uniqueness rule is enforced. Caveat: monotonic surrogates (auto-increment, and to a lesser degree time-ordered ids) can become an insert hot-spot at high write rates — a random UUID spreads writes but hurts index locality, so choose with the write pattern in mind.

6. Name consistently

Naming is not aesthetics — it is the interface every future query and migration reads. One convention (e.g. snake_case, singular-vs-plural table names picked once, customer_id meaning the same thing in every table) removes a class of bugs where someone joins userId to user_id or guesses wrong. Rule: decide the convention once, write it down, and let identical concepts share identical names across tables so joins and reasoning are mechanical.

7. Plan for growth before it hurts

Some structural decisions are cheap up front and brutally expensive to retrofit once a table is huge and hot. Partitioning a 500M-row table by created_at lets the planner prune to one month's partition and makes dropping old data an instant partition-detach instead of a giant DELETE that bloats the table and its indexes. Rule: for tables you can forecast will grow without bound (events, logs, orders), decide the partition key and archival policy while the table is still small; for everything else, don't add partitioning machinery you can't yet justify — it has real operational cost.

Pitfalls

• Index-everything. Every added index taxes every write and consumes cache. Unused indexes are pure cost — audit with pg_stat_user_indexes and drop the dead ones.
• EAV / over-generic schemas. An entity-attribute-value "one table for everything" (entity, attribute, value) throws away types, constraints, and the query planner's ability to help; a simple filter becomes a self-join maze. Model the real entities.
• Premature denormalization. Duplicating columns before you have the hot query and the measured join cost buys nothing and creates a drift problem. Measure first.
• App-only validation. The one write path that skips the check will happen, and the bad row is permanent. Put the invariant in the DB.
• Over-normalization. The opposite failure: shredding data into so many tables that every common read needs a 6-way join. Normalize for correctness, then denormalize the proven hot path — not every path.

Selection & trade-offs (how to decide)

Index or not: add one when a real, selective query needs it and the table is read-heavier on that path than write-heavy; skip it on low-selectivity columns and write-hot tables. Named alternative: a composite/covering index can serve several query shapes with one structure instead of many single-column indexes — fewer writes taxed. Normalize or denormalize: normalize until a specific read is proven hot and the join is the measured cost, then denormalize that path with an automatic sync mechanism — or reach first for the cheaper alternatives (covering index, cache with TTL, read replica) that give the read win without you owning duplicated data.

Takeaways

• Indexes and denormalized copies are redundant structures: they trade write cost and a maintenance burden for read speed. Add them against a proven read, never by default.
• An index helps only when the column is selective and a real query uses it — verify with EXPLAIN, drop what pg_stat_user_indexes shows unused.
• Normalize for one source of truth; denormalize only when a hot query, a measured join bottleneck, and an automatic-sync mechanism all line up.
• Put invariants (types, NOT NULL, keys, CHECK, UNIQUE) in the database so no writer can bypass them; app checks are a convenience, not the guarantee.

---

Re-authored and deepened for this guide, synthesizing Designing Data-Intensive Applications (Kleppmann), Database Internals (Petrov), the PostgreSQL documentation (indexing, EXPLAIN, pg_stat_user_indexes, partitioning), and Use The Index, Luke! (Winand). Re-authored/Deepened for this guide.

I just read a lesson on **Best Practices in Data Modeling** (Databases) and want to truly understand it. Explain Best Practices in Data Modeling from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Best Practices in Data Modeling** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Best Practices in Data Modeling** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Best Practices in Data Modeling** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.