Data modeling and denormalization

Data modeling is the step from “what the product does” to “the tables, documents or items we store”. You name what the system must remember, how those things relate and which questions it must answer fast, then choose keys and layouts so each question is a cheap lookup. Denormalization is one tool in that step: copying or precomputing data on purpose so a hot read need not assemble it.

Picture it first: a kirana store’s notebooks

A kirana store that delivers keeps a customer register (one page per household: name, phone, address) and a delivery slip per order, on which the owner copies the address and that day’s prices. At the top of each register page he pencils a running total of what the household owes. He files slips by customer, because “what did I order last week?” is the question he hears most, and keeps a spike by the door with only today’s undelivered slips.

Map it back:

• Register = normalized entity table: each fact in one place.
• Slip = order with snapshots: when Mrs Rao moves, old slips stay as they were.
• Running total = denormalized aggregate: instant to read, but every slip must update it.
• Filing by customer = the key for the most frequent question.
• Spike = a sparse secondary index holding only undelivered slips.

The modelling process, step by step

Use the same seven steps in every interview; they take about five minutes at the whiteboard.

1. List the entities. Nouns with their own identity and lifecycle: user, address, seller, product, order, review. Anything that can exist several times per parent (home and office addresses) is an entity, not a column.
2. Draw relationships with cardinality. Ask both directions (can one X have many Y; can one Y belong to many X?) to get 1:1, 1:N or M:N, and note which side grows without bound.
3. Write the access patterns with rates. Every read and write, with peak rate, result size, sort order and freshness. This is the step candidates most often skip.
4. Pick the primary key and partition key. The primary key identifies a record; the partition (shard) key decides its machine, so make it the “for whom” of the most frequent query: the user for “my orders”.
5. Index the remaining patterns. Leading columns match the equality filters, then the sort column (composite index design).
6. Decide what to duplicate. Snapshots, hot aggregates, display fields behind expensive joins, each with a named sync path.
7. Sanity-check growth. Rows per day, bytes per row, the biggest partition or document, the hottest key. If a number breaks a limit, go back to step 4.

Start relational designs normalized (database normalization) and break that rule only in step 6.

In a relational database, steps 1 and 2 shape the tables and steps 3 to 5 tune them. In DynamoDB and Cassandra, step 3 shapes everything: AWS says you shouldn’t start designing the schema until you know the questions it must answer, and Cassandra calls it the query-first approach.

Worked example: a Flipkart-style marketplace

The product: users browse products and see each one’s seller and rating, buy from one or more sellers, and look at their orders; sellers see what to pack; buyers write reviews.

One early decision: a cart with items from three sellers becomes three orders, grouped by a checkout ID. Each order then belongs to one seller and ships from one place, so seller queries need no detour through line items. You pay with a slightly more complex checkout.

Entities, relationships and cardinality

• A user has many addresses and many orders (1:N each).
• A seller has many products and many orders (1:N each).
• An order has many order items, and a product appears in many order items. So order items is the join between orders and products, carrying its own facts: quantity and price paid.
• Products and categories are M:N, which needs a join table.
• Users and products each have many reviews, at most one per user per product.

Check every 1:1. “An order has one payment” sounds 1:1, but a declined card followed by a UPI retry gives one order two payment attempts: really 1:N attempts with at most one success.

Here is the core schema in PostgreSQL 18: money as integer paise, time as timestamptz, snapshots and denormalized counters marked.

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE addresses (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users,
  line1   text NOT NULL, city text NOT NULL, pin char(6) NOT NULL
);
CREATE TABLE sellers (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  display_name text NOT NULL
);
CREATE TABLE products (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  seller_id    bigint NOT NULL REFERENCES sellers,
  title        text   NOT NULL,
  price_paise  bigint NOT NULL CHECK (price_paise >= 0),
  rating_count bigint NOT NULL DEFAULT 0,   -- denormalized
  rating_sum   bigint NOT NULL DEFAULT 0    -- denormalized
);
CREATE TABLE categories (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  parent_id bigint REFERENCES categories,
  name      text NOT NULL
);
CREATE TABLE product_categories (           -- M:N join table
  product_id  bigint REFERENCES products,
  category_id bigint REFERENCES categories,
  is_primary  boolean NOT NULL DEFAULT false,
  PRIMARY KEY (product_id, category_id)
);
CREATE TABLE orders (
  id          uuid   PRIMARY KEY DEFAULT uuidv7(),
  checkout_id uuid   NOT NULL,
  user_id     bigint NOT NULL REFERENCES users,
  seller_id   bigint NOT NULL REFERENCES sellers,
  status      text   NOT NULL,
  ship_to     jsonb  NOT NULL,              -- snapshot of the address
  total_paise bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
  order_id         uuid     REFERENCES orders,
  line_no          smallint,
  product_id       bigint   NOT NULL REFERENCES products,
  title            text     NOT NULL,       -- snapshot
  unit_price_paise bigint   NOT NULL,       -- snapshot
  qty              int      NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, line_no)
);
CREATE TABLE reviews (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint   NOT NULL REFERENCES products,
  user_id    bigint   NOT NULL REFERENCES users,
  stars      smallint NOT NULL CHECK (stars BETWEEN 1 AND 5),
  body       text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (product_id, user_id)
);

-- One index per access pattern that a primary key does not cover
CREATE INDEX ON reviews (product_id, created_at DESC);          -- A1
CREATE INDEX ON orders (user_id, created_at DESC);              -- A2
CREATE INDEX ON orders (seller_id, created_at)
  WHERE status IN ('PLACED', 'PACKED');                         -- A4
CREATE INDEX ON product_categories (category_id, product_id);   -- reverse M:N
CREATE INDEX ON order_items (product_id);                       -- FK side

Access patterns with rates

Assume 2 million orders and 300 million product page views a day, sized for a peak of 5× the daily average (design a flash sale handles sale spikes). First row: 300M ÷ 86,400 s ≈ 3,470 views/s, × 5 ≈ 17,000 reads/s; the others use the same formula.

A1 runs 3,000 times as often as A6 (300M ÷ 100k): the case for precomputing the rating. A2 and A4 share one shape, “a range for one owner, sorted by time”, which a composite index or a partition key plus sort key serves.

Growth check

Orders: 2M × 365 ≈ 730M rows a year; at an assumed 400 bytes each (address snapshot included), about 290 GB. Items: 2.5 per order ≈ 1.8 billion rows; at 120 bytes, about 220 GB. Roughly 0.5 TB a year before indexes.

One PostgreSQL primary holds that for a year or two; then partition by month or shard by user_id, since A2 and A3 are per user. Then A4 spans every shard, so a seller-side copy of open orders earns its write cost.

Choosing keys and IDs

Natural vs surrogate keys

A natural key is a real-world identifier: email, phone number, PAN, GSTIN, ISBN. A surrogate key is a meaningless value the system makes up, such as id = 81723.

Natural keys fail in two ways. They change: people change email, mobile numbers get recycled, and RFC 9562 warns that postal codes and licence numbers seem fixed “only later to have edge cases where they need to change”. They are copied everywhere: every foreign key repeats the primary key, and every InnoDB secondary index entry contains the primary key columns, so a long or sensitive key spreads bytes and personal data around.

Default to a surrogate primary key plus a UNIQUE constraint on the natural key (database constraints). Natural keys still suit small reference tables (currency INR) and join tables.

Four ways to generate an ID

Auto-increment (GENERATED ALWAYS AS IDENTITY in PostgreSQL, AUTO_INCREMENT in MySQL). Eight bytes as a bigint, always increasing, so inserts append at the B-tree’s right edge. You pay in coordination: one database hands out the numbers, which breaks down when several shards mint IDs. The numbers also leak volume (order #4,000,123) and invite URL guessing. Avoid 4-byte int, which runs out at 2,147,483,647.

UUIDv4. 128 bits, 122 of them random; any service can generate one. The cost is locality: RFC 9562 says random UUIDs “have poor database-index locality”, with effects on B-trees that “can be dramatic”. Each insert dirties a random leaf page, so the whole index must stay in memory, and splits leave pages part-empty. In InnoDB the primary key is the clustered index, so the rows themselves scatter, and every secondary index carries the 16-byte key.

UUIDv7 (RFC 9562, May 2024). The first 48 bits are a Unix timestamp in milliseconds; after the version and variant bits, 74 bits are random or partly a counter. The bytes sort by creation time, so inserts append while anyone can still generate IDs; the RFC puts the gain over random inserts at “one order of magnitude or more”. PostgreSQL 18 has a built-in uuidv7(). Costs: 16 bytes (store binary uuid, not text), and the ID reveals its creation time.

Snowflake-style 64-bit IDs. Twitter built Snowflake in 2010 while moving tweets from MySQL to Cassandra and sharded MySQL, neither of which had a built-in way to generate unique IDs; it needed uncoordinated, roughly time-sorted IDs in 64 bits. The original layout is 41 bits of milliseconds since a custom epoch, 10 bits of machine ID and 12 bits of sequence, sign bit unused: 2^41 ms ≈ 69.7 years, 1,024 generators, 4,096 IDs per millisecond each. You pay in operations: unique machine IDs (Twitter used ZooKeeper) and clocks that must not run backwards (Snowflake stalls until the clock catches up). See design a unique ID generator.

The same property that helps a B-tree can hurt a shard

Time-ordered keys suit one B-tree, but in a range-sharded store (HBase, Bigtable, CockroachDB, MongoDB with a ranged shard key) they send every new row to the last key range, so one node takes all the writes. In a hash-sharded store (DynamoDB, Cassandra) the partition key is hashed, so sequential IDs spread fine; the trap there is a time-bucket partition key such as day = 2026-10-27, which puts all of today’s writes in one partition.

The fix in both: lead the partition key with something high-cardinality such as user_id (or hash it, or add a shard suffix), and sort by time inside the partition.

Modelling relationships

1:N is a foreign key on the “many” side, such as orders.user_id. Index it: PostgreSQL does not do it for you, and both child lookups and parent deletes need it (foreign key design).

1:1 usually means the same table. Split only when one half is optional, large or cold (KYC documents beside a hot seller profile), using the same key in both.

M:N needs a join table keyed by both foreign keys, product_categories(product_id, category_id), plus the reverse index (category_id, product_id). Join tables often grow attributes of their own: follows(follower_id, followee_id, created_at), or order_items with quantity and price.

Other models change the mechanics. A document embeds a bounded “one-to-few” and otherwise stores the parent’s ID in each child. DynamoDB puts a 1:N in one item collection (same partition key, different sort-key prefixes) and an M:N in an adjacency list: one item per edge, plus a global secondary index (GSI) with the keys swapped. Cassandra keeps one table per direction.

Snapshot vs reference

order_items.unit_price_paise and orders.ship_to look like copies of the product price and an address, but they record what was true at purchase. Asha paid ₹349 for the bottle on 27 October and it went to her old flat. If the seller raises the price to ₹379 next week, or Asha deletes that address, the order and its GST invoice must not change.

They are written once and never synced; MongoDB’s guide uses the same example: a customer’s address at the time of an order is worth duplicating because its historical value matters.

The same order modelled three ways

Relational: normalized tables plus indexes

The DDL above is the relational model: A3 is two primary-key lookups, A2 a range scan that stops after 20 rows, and checkout one transaction. A new question next quarter needs an index, not a redesign.

Document: embed what is read together, reference what is shared

In MongoDB an order is a natural document: it is read whole, its line items are bounded, and nothing else points into them.

{
  "_id": "01a17cdb-3458-7c2e-9b41-5d07a3e159f3",
  "checkoutId": "chk_81723",
  "userId": 42,
  "sellerId": 7,
  "status": "PLACED",
  "createdAt": "2026-10-27T10:15:03Z",
  "shipTo": { "name": "Asha Rao", "line1": "12 MG Road", "city": "Bengaluru", "pin": "560001" },
  "items": [
    { "productId": 9001, "title": "Steel bottle 1L", "qty": 2, "unitPricePaise": 34900 },
    { "productId": 7710, "title": "Lunch box", "qty": 1, "unitPricePaise": 39900 }
  ],
  "totalPaise": 109700
}

The rules behind that shape, from MongoDB’s embedding vs referencing guidance:

• Embed when the data is read and updated together, “contained” by the parent, and bounded in count. A single-document write is atomic, so order and items change together without a multi-document transaction.
• Reference when the children are unbounded, shared (M:N), queried on their own, or would change often as copies.

Size makes “bounded” concrete. A document must be smaller than 16 MiB, so embedding every order (about 1 KiB each) in the user would cap a heavy B2B buyer at about 16,000 orders (16 MiB ÷ 1 KiB = 16,384), and every profile read would drag them along. So orders get their own collection, products are referenced by productId, and reviews live apart, with only the rating summary embedded in the product.

Query-first: DynamoDB single-table design

In DynamoDB the keys come from the access patterns. Single-table design stores several entity types under generic key names (PK, SK, GSI1PK, GSI1SK) so each pattern is one GetItem or one Query over a contiguous range of sort keys.

Choices worth saying out loud:

• Line items embedded in the order item. An order is read whole and is far below DynamoDB’s 400 KB item limit, so A3 is one GetItem; AWS’s modelling guide calls this aggregate design.
• A sparse index for A4. Only open orders carry GSI1PK; removing it at shipping drops the order from the index. GSI reads are eventually consistent, normally within a fraction of a second.
• The hot partition. A partition serves at most 3,000 read and 1,000 write units per second. A mega-seller taking 2,000 orders a second would exceed that on SELLER#7#OPEN, so add suffixes #0 to #9 and query all ten in parallel.
• Uniqueness needs its own item. The review key does not stop a second review; a guard item PRODUCT#9001 / REVIEWER#u42, written in the same transaction with an “attribute does not exist” condition, does.

Query-first: Cassandra’s one table per query

Cassandra has no joins, and its secondary indexes are local to each node, so a query without a partition key asks many nodes. Its documentation designs one table per query (hotels_by_poi): the partition key answers “for which X”, clustering columns give the order.

-- Cassandra CQL: one table per access pattern, both written at checkout
CREATE TABLE orders_by_user (
  user_id     bigint,
  order_id    timeuuid,
  seller_id   bigint,
  status      text,
  total_paise bigint,
  PRIMARY KEY ((user_id), order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);

CREATE TABLE open_orders_by_seller (
  seller_id bigint,
  day       date,            -- bounds each partition to one day
  order_id  timeuuid,
  user_id   bigint,
  PRIMARY KEY ((seller_id, day), order_id)
);

Discord’s message store uses the same reasoning: its key is ((channel_id, bucket), message_id), where message_id is a Snowflake for time order and bucket is a roughly 10-day window, added when long-lived channels produced partitions over 100 MB. Step 7, the growth check, forced a change in step 4.

Denormalization as a modelling tool

Denormalization stores a fact, or something derived from facts, in more than one place so a hot read can skip a join or aggregation. Three forms cover most cases.

Precomputed aggregates. products.rating_count and rating_sum let the product page show “4.3 ★ from 12,480 ratings” without reading 12,480 rows. Store sum and count, not the average, so a review is two increments. The cost: one extra update per review (about 6 a second) instead of aggregating on 17,000 page views a second.

-- A6: the review and the counters change in one transaction
BEGIN;
INSERT INTO reviews (product_id, user_id, stars, body)
VALUES (9001, 42, 4, 'Keeps water cold all day');
UPDATE products
SET rating_count = rating_count + 1,
    rating_sum   = rating_sum + 4
WHERE id = 9001;
COMMIT;
-- A1 shows rating_sum::numeric / NULLIF(rating_count, 0)

The in-place increment means simultaneous reviews cannot overwrite each other (the lost update problem).

Copied display fields. The seller’s name on every product card saves a join on the busiest read, but a seller with 50,000 listings turns a rename into 50,000 updates: fine as a background job, terrible inside the request.

Per-query tables and indexes. A Cassandra orders_by_user, a DynamoDB GSI, a seller-side copy of open orders after sharding: each is a copy laid out for one query. DynamoDB syncs a GSI for you; your own tables are your problem.

Every copy costs extra writes, a window of disagreement and repair code. How to keep copies in sync (same transaction, trigger, change data capture, transactional outbox, rebuild) and when it pays are covered in normalization vs denormalization. The modelling rule: denormalize for a named access pattern whose read rate dwarfs the copy’s write rate, and name the source of truth.

Going deeper: modelling mistakes and schema evolution

Mistakes interviewers notice

Entity-attribute-value (EAV) tables. product_attrs(product_id, attr_name, attr_value text) looks flexible but has no types (is weight 500, “500 g” or “0.5kg”?), no constraints, and needs a 20-way pivot to read 20 attributes. Use typed columns plus an indexable jsonb column for category-specific specs.

Polymorphic “anything” columns. comments(target_type, target_id) cannot have a foreign key, so orphans creep in. Use one nullable foreign key per target with a CHECK that exactly one is set, or a join table per target (foreign key design).

Unbounded arrays in a document. A followers array on a celebrity grows towards the 16 MiB limit, and every update rewrites a large document. No ceiling on the children means reference them.

Money as a float. real and double precision are inexact: 0.1 + 0.2 is not exactly 0.3 in binary, so totals drift by paise. Use integer minor units (bigint paise) or numeric(12,2), which PostgreSQL recommends for money, plus a currency code.

Timestamps without a time zone. PostgreSQL’s plain timestamp silently ignores a time zone in the input, so “10:15” is ambiguous between IST and UTC once servers or regions disagree. timestamptz converts input to UTC and stores one absolute instant.

Over-generic schemas. An entities plus relations pair, or one data jsonb column for everything, hides the schema in application code and loses constraints, indexes and planner statistics.

Missing uniqueness. “One review per user per product” must be UNIQUE (product_id, user_id), not an API if that two concurrent requests both pass.

Schema evolution basics

• Additive changes are cheap: a nullable column, a new table, an index built concurrently.
• Renames and type changes use expand and contract: add, dual-write, backfill, switch reads, drop (online schema and data migrations).
• Documents carry a version: a schemaVersion field, upgraded on read or in a background pass (serialization and schema evolution).
• In DynamoDB, attributes are free but keys are not. A new GSI is backfilled for you; a new PK or SK layout rewrites every item.

Cheat sheet

How to say it in the interview

“The entities are users, addresses, sellers, products, orders, order items and reviews; orders are one-to-many to items, products many-to-many to categories through a join table. The hot patterns are the product page at about 17k reads a second, ‘my orders’ newest first, and the seller’s open orders. In PostgreSQL I keep it normalized with surrogate keys, UUIDv7 for order IDs so inserts stay at the index’s right edge, and one composite index per pattern. I duplicate two things on purpose: the price and address on the order, which are snapshots, and the rating count and sum, updated in the review’s transaction. If we shard by user, the seller view becomes a CDC-fed copy. In DynamoDB, orders sit under USER# partitions sorted by date, with a sparse GSI for open orders.”

Embed or reference? Embed what is read with the parent, owned by it and bounded (an order’s line items); reference what is unbounded, shared, read alone or changing often (products, reviews). The 16 MiB limit is the hard stop; rewriting huge documents hurts much earlier.

Why not UUIDv4 as the primary key? Its random bits put each insert on a random B-tree leaf, so the whole index becomes the working set and pages split; in InnoDB the table itself is clustered in that order, with 16 bytes in every secondary index. UUIDv7 leads with a millisecond timestamp, so inserts append. UUIDv4 is fine as a hash-partition key or opaque token.

How do you model many-to-many in DynamoDB? With an adjacency list: one item per edge, such as PK = CATEGORY#kitchen, SK = PRODUCT#9001, often with display attributes copied in. A GSI with PK and SK swapped answers the reverse, “which categories is product 9001 in?”. Use TransactWriteItems when an entity and its edges must agree.

When would you denormalize the rating? When reads dwarf writes and the aggregate is expensive: 17,000 page reads a second against about 6 reviews. I keep rating_count and rating_sum, update them in the review’s transaction, and recompute nightly to repair drift. If one product drew hundreds of reviews a second, I would batch the increments asynchronously and accept seconds of staleness.

Where to go next

• Normalization vs denormalization: the full trade-off, sync mechanisms and a checklist.
• Database normalization: the normal forms behind “store each fact once”.
• SQL vs NoSQL and database types: when a document or wide-column model is the right starting point.
• Database sharding: choosing the shard key that step 4 points at.
• Cassandra and DynamoDB: partitions, sort keys and GSIs in production.
• Design a unique ID generator: building Snowflake-style IDs end to end.

References and further reading

1. RFC 9562: Universally Unique IDentifiers (UUIDs) (IETF, May 2024)rfc-editor.orgDefines UUIDv4 (122 random bits) and the time-ordered UUIDv7, and explains why random keys have poor B-tree index locality.
2. Snowflake, 2010 release README (Twitter, GitHub)github.comThe original 64-bit ID layout: 41 bits of milliseconds, 10 bits of machine ID, 12 bits of sequence, and the behaviour when the clock runs backwards.
3. MongoDB Manual: Data modeling best practicesmongodb.comWhen to embed and when to reference, and which kinds of duplicated data are safe, including an address kept as it was at order time.
4. MongoDB Manual: Embedded datamongodb.comEmbedding for single-operation reads and atomic single-document writes, and the 16 MiB document size limit.
5. Amazon DynamoDB Developer Guide: NoSQL design for DynamoDBdocs.aws.amazon.comKnow the access patterns before designing the schema; keep related data together, use sort order and global secondary indexes.
6. Amazon DynamoDB Developer Guide: Designing partition keysdocs.aws.amazon.comPer-partition limits of 3,000 read units and 1,000 write units per second, and why load must be spread across partition keys.
7. Amazon DynamoDB Developer Guide: Many-to-many relationshipsdocs.aws.amazon.comThe adjacency-list pattern: one item per edge, with a global secondary index on the sort key for the reverse direction.
8. Apache Cassandra documentation: Logical data modelingcassandra.apache.orgQuery-first design with one table per query, partition keys and clustering columns, and the wide-partition pattern.
9. PostgreSQL documentation: Numeric typespostgresql.orgnumeric is recommended for monetary amounts; real and double precision are inexact IEEE 754 types.
10. How Discord Stores Billions of Messages (Discord engineering, 2017)discord.comA real query-first model: Snowflake message IDs as clustering keys and 10-day buckets to keep Cassandra partitions under 100 MB.