Data modeling

The data model is the part of a design that is hardest to change later. Code can be rewritten in a week. A billion rows in the wrong shape take months to move. So the model is worth a few careful minutes.

Crux: start from the questions the system must answer, model entities and relationships to serve them, and copy data (denormalise) only when a read is measurably too costly and you accept the cost of keeping copies in step.

The order of work

• 1. List the entities. The nouns in the requirements: user, link, order, driver.
• 2. Draw the relationships. Who owns what, and how many of each.
• 3. Write down the access patterns. The exact reads and writes, with a rate: "get a link by code, 12,000 per second at peak", "list a user's orders, newest first".
• 4. Choose keys and indexes so each access pattern is one cheap lookup.
• 5. Revisit when a number changes.

Step 3 is the one people skip. The access patterns decide the model, not the other way round. The same entities can be stored three different ways depending on how they are read.

Put only the fields the requirements need in the first draft. It is easy to add a column later and hard to defend ten you do not use.

Pick the database family from the access patterns

Name the property you need, not a brand. Defaults first.

• Relational (Postgres, MySQL). The default. Joins, transactions, constraints, and enough scale (replicas, caching, later sharding) for most designs. If you have no reason against it, say so and move on.
• Document (MongoDB, Firestore). Records that are nested and read whole, with a shape that varies or changes often. Embedding saves joins. Cost: changing one small part of a large nested record is awkward, and many-to-many links are painful.
• Key-value (Redis, DynamoDB used simply). One key in, one value out, very fast. Often a cache in front of the real store, or a store for sessions and counters. No queries beyond the key.
• Wide-column (Cassandra, Bigtable). Very high write rates and time-ordered data, spread across many nodes. You design the table around one query, and joins are not available.
• Graph (Neo4j). Questions about paths through relationships (friends of friends, fraud rings). Rarely the right answer in an interview; a relational table of edges usually does the job at the sizes discussed.

The cost of any non-default pick is a sentence you must be ready to say: "I give up joins and ad hoc queries to get write throughput."

Relationships

• One to one. A user has one profile. Often just more columns in the same row.
• One to many. A user has many links. The "many" side holds the foreign key (links.owner_id).
• Many to many. Students and courses. A third table holds the pairs (enrolments(student_id, course_id)), and it can carry facts about the pair, such as the date.

Cardinality matters for scale: "a user has many links" is fine. "A link has many clicks, and one link can get a million" means the clicks cannot live inside the link row.

Normalise first, then copy on purpose

Normalised means each fact is stored once, and other rows point to it by key. Denormalised means a fact is copied to where it is read.

• Normalised: updates are easy and consistent (change the name once). Reads need joins, which cost time at scale.
• Embedded or denormalised: a read is one lookup. Writes must update every copy, and the copies can drift apart.

Arithmetic for the cost of a copy: if a user's name is copied into every order and the user has 500 orders, a rename is 500 writes. That is fine for a rename (rare) and not fine for a counter that changes on every click.

Rules of thumb:

• Default to normalised in a relational database. Joins on indexed keys are fast and the guarantees are worth a lot.
• Copy when a read is hot and the copied fact rarely changes: the author name on a post, the product title on an order line (and for orders you often want a snapshot of the price as it was).
• Store precomputed values (a click count on the link) when counting rows every time is too slow, and say how you keep them right.
• In key-value and wide-column stores you usually cannot join at all, so you design one table per access pattern and write the data into each. State this as the cost of the choice.

Choose keys with care

• Primary key: unique, never changes, small. A surrogate key (a generated ID) is the safe default. A natural key (email, phone) changes and leaks.
• Auto-increment integers are compact and sort by time. They are guessable (a competitor can read your growth from the numbers) and every write goes to the last page of the index, which becomes a hot spot when sharded.
• Random UUIDs are not guessable and are easy to generate anywhere, so you need no central counter. They are 16 bytes against 4 or 8, and random inserts land all over the index, which is slower for a B-tree.
• Time-sortable IDs (a timestamp prefix plus random bits, such as UUIDv7 or a snowflake-style ID) give both: unique without coordination, and roughly in time order.
• A short code such as a link code has its own rules. Seven characters from 62 symbols give 62^7 ≈ 3.5 trillion values (62^7 = 3,521,614,606,208), far more than the 1.2 billion links a year in the estimation lesson.

For the shard key (next lessons) pick the field most queries filter on, and remember you cannot change it cheaply.

Habits that save pain later

• Store money as integers in the smallest unit plus a currency. Floating point cannot hold 0.10 exactly.
• Store time in UTC and convert at the edge. Store the instant, not the local string.
• Add created_at and updated_at to everything. They answer most "what happened?" questions for free.
• Prefer soft delete (a deleted_at column) when you may need to undo, audit or comply with a retention rule. Hard delete when the data must truly go. Say which, and why.
• Constrain the data in the database: NOT NULL, UNIQUE, foreign keys. Application code has bugs, the constraint is the last line of defence. Constraints cost write time (each insert is checked), and foreign keys across shards cannot be enforced at all. At very large scale teams often drop foreign keys and check in code; say that this trades safety for write speed and sharding freedom.
• Index for the query, not the column. Each access pattern names the index that serves it (see the indexing lesson), including the composite order.
• Avoid time as the leading shard key on write-heavy data: every new write lands on the newest shard. Shard by the field most queries filter on, and keep a query that must read many shards (a global timeline) off the main path.
• Plan for append-only data. Events, clicks and logs are written once and never edited. Keep them in their own table or store, partitioned by time, so old data can be dropped or archived cheaply.

A worked example: the link shortener

Access patterns, from the estimation lesson: look up a link by code (about 12,000 per second at peak), create a link (about 40 per second), record a click (about 4,000 per second), list a link's clicks (rare).

links    ( code PK, url, owner_id, created_at, expires_at )
clicks   ( code, clicked_at, ... )        -- append-only, partitioned by time
users    ( id PK, ... )
INDEX links (owner_id, created_at)        -- "my links, newest first"
INDEX clicks (code, clicked_at)           -- "clicks for this link"

• The code is the primary key, so the hot read ("code to URL") is one lookup by primary key.
• A link's click count is not computed with COUNT(*) on every view. Keep a counter that is updated in batches, or compute it from the click log. The trade-off: the counter may lag by seconds.
• clicks is the table that grows (about 12.6 TB a year in the estimation example), so it is the one to partition by time and, later, to shard.

The checklist to run at the whiteboard

• 1. Database family and why.
• 2. Tables or collections, with only the columns the requirements need.
• 3. Primary key for each, and why it is stable.
• 4. Indexes, one per hot access pattern.
• 5. Copies: which fields are denormalised, and what keeps them right.
• 6. Sharding: only if the numbers force it, and by which key.

Stay on this for a few minutes. The model supports the design; it is not the whole interview.

Common mistakes

• Designing tables before writing the access patterns.
• Picking an exotic database (graph, wide-column) with no requirement that needs it.
• Denormalising on day one, before any read is known to be slow.
• Time-range sharding for a write-heavy table.
• Over-modelling: twenty tables when the requirements need four.
• Using one big table that holds every entity because "it is flexible".
• Normalising everything in a store that cannot join, or denormalising everything in one that can.
• Copying a field that changes often.
• Natural keys that later change (email, phone).
• Floats for money, local times for instants.
• No constraints, so bad data gets in and nobody finds out.

Check yourself

• Why do you write the access patterns before the schema?
• What does a rename cost in a normalised model and in an embedded one?
• Give one reason to copy a field and one reason not to.
• Why can a random UUID be slower than a sequential ID as an index key, and what is the middle path?
• Where should the click counter for a link live, and what does it cost?
• Why store money as integers?
• Name one workload for each of relational, key-value and wide-column, and what each gives up.
• What do foreign keys buy, and why do some large systems drop them?