Data modeling series: 1. The data modeling process · 2. JSON columns, online DDL, data types and views · 3. Case studies: translations, ordering, calendars and reports
The schema is the hardest thing to change in a backend: bad code is a redeploy, a bad schema is a migration of hundreds of millions of rows while the system keeps serving traffic. This post is the first half of the process: start from the queries, classify the tables, decide how far to normalize, keep or drop foreign keys, place reference columns, and apply a table template. Part 2 covers JSON columns, zero-downtime schema changes, data types and views; part 3 applies all of it to six concrete problems.
Read first: normalization, 1NF to 5NF for the theory of normal forms and indexes and EXPLAIN for composite indexes. Every number was measured on PostgreSQL 16 and MySQL 8.4 (Docker on an Apple Silicon laptop); read them as ratios, not benchmarks.
1. The process: start from access patterns, not from tables
Key idea: list the main queries and writes (with frequency and latency targets) first, then group fields into tables. Tables drawn before the queries look reasonable but may not answer the product’s most important question fast.
An access pattern is how data gets read and written: which query, what it filters on, what it sorts by, how many times per second. Three phases, in this order:
- Analysis: requirements, the access pattern list, the existing schema (when changing a legacy system), a rough field list.
- Modeling: group fields into tables, refine, choose keys and relationships.
- Data integrity: review, decide how far to normalize and where to denormalize.
The common mistake: jumping straight to phase 2, opening an ERD tool and dragging out ten boxes.
Example: a multi-author blogging platform
| # | Query | Frequency | Target |
|---|---|---|---|
| Q1 | Feed: the 20 newest posts by authors from one country | 300 req/s | p95 < 50 ms |
| Q2 | Post details with the comment count | 1,000 req/s | p95 < 20 ms |
| Q3 | Author dashboard: my posts, newest first | 20 req/s | p95 < 200 ms |
| W1 | Publish a post | 5 req/s | p95 < 300 ms |
| W2 | Add a comment | 50 req/s | p95 < 100 ms |
The rough fields group themselves (author, post, comment) and become tables. The query table also reveals two things an ERD hides:
- Q1 filters on the author but sorts on the post: a sign that denormalization is needed (numbers in section 3).
- Q2 is read 20 times as often as W2 writes: a comment counter column is worth it.
2. Entities, ER diagrams and four kinds of tables
Key idea: an entity has an identity and a lifecycle; every other table is a relation, support or config table. Knowing the kind tells you which columns the table needs.
An entity has an identity (an id that never changes) and a lifecycle (usually a status column). A post is an entity: created, published, hidden, deleted. A country in a lookup list has no meaningful lifecycle; it is configuration.
Treat status as a state machine (a fixed set of states and allowed transitions), not a free-form string:
stateDiagram-v2
[*] --> DRAFT
DRAFT --> PUBLISHED: publish
PUBLISHED --> HIDDEN: moderator hides
HIDDEN --> PUBLISHED: restore
DRAFT --> DELETED: delete
PUBLISHED --> DELETED: delete
DELETED --> [*]
- The database guards the set of valid values:
CHECK (status IN (...))or an enum, see enums in the database and the API. - Valid transitions are usually checked in the domain layer, plus a condition like
UPDATE ... WHERE status = 'DRAFT'so two concurrent requests can’t both make the transition.
The slides draw the ERD (entity-relationship diagram) in Chen notation (boxes, diamonds, ovals; students N–M classes, class N–1 teacher). Crow’s foot notation (Mermaid erDiagram) is more compact and closer to the tables.
| Table kind | Role | Examples | Traits |
|---|---|---|---|
| Entity | Business object with a lifecycle | author, post, booking |
Has status, version, audit columns |
| Relation | Links an N–M relationship | post_tag (post_id, tag_id) |
Composite primary key, usually no own id |
| Support | Side data of an entity, split out because it is 1–N or large | post_revision, author_address |
Foreign key to the parent entity |
| Config | Rarely changing lookup data | country, currency, system_config |
Read-heavy, cacheable, often a natural key |
Conventions: entity tables use the full template (section 5), relation tables only need created_at, config tables use a natural key (country_code char(2)) and are cached in the app.
3. Normalize or denormalize: a decision framework
Key idea: normalize as the baseline, then denormalize on purpose where you measured a need, typically when a hot query filters on one table and sorts on another. The price is keeping the redundant data consistent yourself.
The theory of normal forms is in the normalization post. The two sides:
- Normalization (splitting a big table into related small ones): little duplication, good integrity; but you need JOINs, and some queries have no index that serves them end to end.
- Denormalization (merging tables, copying redundant data): fast reads, an index over columns that used to live in two tables; but more storage, and consistency is your job.
For Q1 (100,000 authors, 2 million posts, SG only 0.1% of authors), the same SQL:
| Approach | Country | Buffers read | Time |
|---|---|---|---|
JOIN post–author, filter on author |
VN (60% of authors) |
reads 32 posts | 0.2 ms |
JOIN post–author, filter on author |
SG (0.1%) |
125,000 (≈41,000 posts) | 63 ms |
Column post.author_country + index (author_country, created_at) |
SG |
23 | 0.074 ms |
The price: when an author changes country, every one of their posts must be updated.
The SQL and query plans for both approaches
-- Normalized: filter on author, sort on post
SELECT p.post_id, p.title
FROM post p JOIN author a USING (author_id)
WHERE a.country = 'SG'
ORDER BY p.created_at DESC LIMIT 20;
PostgreSQL walks the post (created_at) index backwards and checks each author. For VN it reads 32 posts to collect 20 results. For SG it reads about 41,000 posts and 125,000 buffers before it has 20 rows. Latency depends on the parameter value.
SELECT post_id, title FROM post
WHERE author_country = 'SG'
ORDER BY created_at DESC LIMIT 20;
-- Index Scan Backward using post_country_created_idx
-- Buffers: 23, Execution Time: 0.074 ms
Keeping a counter column consistent
Key idea: update the counter (comment_count) atomically in SQL, via the same transaction, a trigger or an event; never read it into the app, add one and write it back.
| Approach | Consistency | Cost |
|---|---|---|
Same transaction: SET comment_count = comment_count + 1 |
Strong | Every write path must remember it; hot rows contend for the lock |
| Database trigger | Strong | Hidden logic, hard to debug (exercise 2) |
| Asynchronous event (outbox, Kafka) | Eventual | Lag; needs idempotency and reconciliation |
4. Foreign keys: keep them or drop them
Key idea: keep foreign keys by default when the tables share a database and a service. The real cost isn’t the existence check but the lock on the parent row, which is much heavier in InnoDB.
The slides give the author’s personal view: drop foreign key constraints and check in the application. The view is common at companies running sharded MySQL, but it shouldn’t be the default: three of the slides’ four reasons no longer hold.
The slides’ four reasons to drop foreign keys, and the rebuttal
| Slide’s reason | Reality |
|---|---|
| The database checks the parent key on every child write | One lookup in the parent’s primary key index, usually already in RAM |
| The app has to parse exceptions to learn why a write failed | Spring already translates it into DataIntegrityViolationException carrying the constraint name |
| Backup and restore must follow table order | pg_dump and mysqldump already handle it |
| The schema gets more complex | True, but in exchange you never get orphan rows |
The slides also make a correct point: a foreign key (the constraint) is not the reference column; drop the constraint and the customer_id column stays and still needs an index.
Parent-row locks: PostgreSQL and InnoDB differ
Key idea: while a new child row is uncommitted, PostgreSQL only blocks deleting or re-keying the parent row; InnoDB blocks every UPDATE of the parent row.
Session A inserts an order for customer 1 and doesn’t commit; session B works on customer 1’s row (lock_timeout = 1s):
| Session B’s statement | PostgreSQL 16 | MySQL 8.4 (InnoDB) |
|---|---|---|
UPDATE customer SET name = ... (key unchanged) |
Runs at once (0.7 ms) | Blocked, error 1205 after 1 second |
DELETE FROM customer WHERE id = 1 |
Blocked | Blocked |
UPDATE customer SET id = 10 |
Blocked | Blocked |
Application-level checks have a race condition
Key idea: SELECT to see whether the parent exists, then INSERT, lets an orphan through when the parent is deleted in between. Doing it right means locking the parent row (SELECT ... FOR SHARE), i.e. rewriting what the foreign key already does.
An orphan row is a child row pointing to a parent row that no longer exists.
The race-condition experiment: with and without a foreign key
On PostgreSQL, with an orders table without a foreign key:
- Session A runs
BEGIN, sees that customer 2 exists, sleeps 2 seconds. - Meanwhile session B deletes customer 2 and commits.
- Session A inserts an order for customer 2 and commits successfully: an orphan order.
Repeat with the foreign key in place: session B can still delete (A hasn’t inserted yet), but A’s insert is rejected with violates foreign key constraint.
Which table holds the reference column
Key idea: in a 1–1 relationship, the entity created later holds the reference to the entity created earlier, with UNIQUE to keep it 1–1.
The slides’ problem: placing an order creates an order, then a payment transaction; donating creates only a transaction, no order. Order and transaction are 1–1. The answer: put order_id in the transaction table.
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL,
total bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE payment_txn (
txn_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
purpose text NOT NULL CHECK (purpose IN ('ORDER', 'DONATION')),
order_id bigint REFERENCES orders, -- NULL for donations
campaign_id bigint, -- donations only
amount bigint NOT NULL CHECK (amount > 0),
created_at timestamptz NOT NULL DEFAULT now(),
CHECK ((purpose = 'ORDER') = (order_id IS NOT NULL)),
UNIQUE (order_id) -- keeps it 1–1
);
- When the order is created, the transaction doesn’t exist yet; a
txn_idinordersforces an insert with an empty column and a later update. - New transaction kinds (wallet top-up, refund) never touch
orders: that is what the slides mean by “better for extensibility”. - Drop
UNIQUE (order_id)and it becomes 1–N (multiple payments) without changing any column.
5. A table template: business columns and technical columns
Key idea: an entity table has business columns (id, its own columns, status, a data JSON column) and technical columns (version, created_at/by, updated_at/by). Each technical column has one job; a table that doesn’t need the job drops the column.
CREATE TABLE app_user (
user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-- business
name varchar(100) NOT NULL,
birth_date date,
status text NOT NULL DEFAULT 'ACTIVE'
CHECK (status IN ('ACTIVE', 'LOCKED', 'CLOSED')),
data jsonb NOT NULL DEFAULT '{}',
-- technical
version int NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
created_by text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now(),
updated_by text NOT NULL
);
| Column | What it is for |
|---|---|
version |
Optimistic locking: WHERE id = ? AND version = ?, 0 rows means someone changed it first |
created_at |
Sorting, keyset pagination, time-based partitioning |
created_by, updated_by |
Who (user, admin, service, job) created or changed it |
updated_at |
Incremental sync to other systems (CDC, search index) |
- The slides use
age SMALLINT; storebirth_dateinstead, because age changes every year. - MySQL has
ON UPDATE CURRENT_TIMESTAMP(3)forupdated_at; PostgreSQL needs aBEFORE UPDATEtrigger (exercise 1) or the ORM. - Append-only tables (ledgers, event logs) need neither
updated_atnorversion; relation tables only needcreated_at. - The
dataJSON column has its own limits (part 2). Soft delete (deleted_at) is a separate decision with a cost for unique indexes and every query (soft delete).
Spring Boot 3 code: AuditableEntity and AuditorAware
JPA and Spring Data handle most technical columns:
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AuditableEntity {
@Version
private int version;
@CreatedDate
@Column(nullable = false, updatable = false)
private Instant createdAt;
@CreatedBy
@Column(nullable = false, updatable = false)
private String createdBy;
@LastModifiedDate
@Column(nullable = false)
private Instant updatedAt;
@LastModifiedBy
@Column(nullable = false)
private String updatedBy;
}
@Configuration
@EnableJpaAuditing
class AuditConfig {
@Bean
AuditorAware<String> auditorAware() {
// the logged-in user, or the service/job name when running in the background
return () -> Optional.ofNullable(SecurityContextHolder.getContext().getAuthentication())
.map(Authentication::getName)
.or(() -> Optional.of("system"));
}
}
6. Beyond relational: NoSQL modeling also starts from access patterns
Key idea: in a key-value or document store, a query you didn’t design for is nearly impossible to answer; the access pattern list from section 1 effectively is the schema. (Not in the slides; I added it for contrast.)
A relational database lets you normalize first and add indexes later, because SQL can answer any query; DynamoDB, Cassandra and MongoDB can’t.
How Cassandra, DynamoDB and MongoDB model around queries
- Cassandra, DynamoDB: each main query usually gets its own table (or index) with a partition key and sort key chosen for it; data is duplicated across tables. DynamoDB’s “single-table design” puts several entity types in one table, distinguished by key prefixes (
AUTHOR#42,POST#2026-11-18#981), so one read fetches a whole cluster of items. - MongoDB: embed data that is always read together and bounded in size (a user’s addresses); reference data that grows without bound (comments on a viral post).
Exercises
Exercise 1: Apply the table template to the flight booking schema
Task (paraphrased): the slides’ homework is to design a flight booking schema using the table template. The full schema already exists in the flight booking database design post and the booking and ledger post, so here we do the missing part: apply the template to flight, booking and entry, and explain which technical columns fit and which don’t.
Solution to exercise 1: the template for flight, booking, entry
Run on PostgreSQL 16:
CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END $$;
CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION '% is append-only, % is not allowed', TG_TABLE_NAME, TG_OP;
END $$;
-- booking: aggregate root, with data jsonb for sales-channel extras
CREATE TABLE booking (
booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
booking_code char(6) NOT NULL UNIQUE,
user_id bigint NOT NULL,
status text NOT NULL DEFAULT 'PENDING_PAYMENT',
total_amount bigint NOT NULL CHECK (total_amount >= 0),
currency char(3) NOT NULL DEFAULT 'VND',
data jsonb NOT NULL DEFAULT '{}',
version int NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
created_by text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now(),
updated_by text NOT NULL
);
CREATE TRIGGER booking_touch BEFORE UPDATE ON booking
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
-- entry: ledger entries, append-only
CREATE TABLE entry (
entry_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
txn_id bigint NOT NULL,
account_id bigint NOT NULL,
amount bigint NOT NULL CHECK (amount <> 0),
created_at timestamptz NOT NULL DEFAULT now(),
created_by text NOT NULL
);
CREATE TRIGGER entry_append_only BEFORE UPDATE OR DELETE ON entry
FOR EACH ROW EXECUTE FUNCTION forbid_change();
Checks:
- Create a booking with
version = 0. The payment service’sUPDATE ... SET status = 'PAID', version = version + 1 ... WHERE booking_id = 1 AND version = 0returns 1 row. - A stale request with the same
version = 0condition returns 0 rows (the lost update is detected);updated_atis now later thancreated_atthanks to the trigger. UPDATE entryis rejected withentry is append-only, UPDATE is not allowed.
Each decision:
flight(DDL as in the original table plusversion,created_*,updated_*and thetouch_updated_attrigger, not repeated here): the full set of technical columns, because flight times are edited by operations staff and by the schedule sync job;updated_byanswers “who changed the time of VN213”. Nodatacolumn: flight attributes are stable and all need filtering.booking: the full set, anddata jsonbfits sales-channel details (agent code, UTM, device) that are sparse and never filtered.versionis mandatory because several parties modify a booking (customer, payment service, expiry job).created_bylooks like a duplicate ofuser_idbut isn’t: a call-center agent can create a booking on a customer’s behalf.entry: onlycreated_atandcreated_by. A ledger is never edited: mistakes are fixed with a reversing entry.updated_*orversionwould imply edits are allowed, so they go; the trigger blockingUPDATEandDELETEturns the convention into a constraint. In production, alsoREVOKE UPDATE, DELETEfrom the application’s role.
Exercise 2: A comment counter maintained by a trigger
Task: keep article.comment_count always equal to the real number of comments, even with concurrent writers and someone inserting through manual SQL.
Solution to exercise 2: the counter trigger and the search_path bug
Run on PostgreSQL 16:
CREATE TABLE article (
article_id bigint PRIMARY KEY,
title text NOT NULL,
comment_count int NOT NULL DEFAULT 0 CHECK (comment_count >= 0)
);
CREATE TABLE comment (
comment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
article_id bigint NOT NULL REFERENCES article,
body text NOT NULL
);
CREATE INDEX ON comment (article_id);
CREATE FUNCTION sync_comment_count() RETURNS trigger
LANGUAGE plpgsql SET search_path = public AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE article SET comment_count = comment_count + 1 WHERE article_id = NEW.article_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE article SET comment_count = comment_count - 1 WHERE article_id = OLD.article_id;
END IF;
RETURN NULL;
END $$;
CREATE TRIGGER comment_count_sync AFTER INSERT OR DELETE ON comment
FOR EACH ROW EXECUTE FUNCTION sync_comment_count();
Result: 5 inserts and 1 delete give comment_count = 4, matching count(*). 8 parallel sessions with 25 inserts each: 204 matches 204, because UPDATE ... + 1 locks the article row and serializes the increments.
On the first run the function had no SET search_path while the tables lived in their own schema, and every insert from another session failed with relation "article" does not exist: unqualified names are resolved with the calling session’s search_path. Always pin search_path in trigger functions.
All comments on one article lock the same article row. A viral post needs slot counters or batched event increments, plus a job reconciling count(*) with the counter.
Remember
- Explain why the access pattern list (frequency, p95) comes before the ER diagram.
- Classify a table as entity, relation, support or config, and know which technical columns each kind needs.
- Know when to add a redundant column (filter on table A, sort on table B) and the three conditions that make it safe.
- Compare parent-row locking on a child insert in InnoDB and PostgreSQL, and know when to drop a foreign key.
- Know where the 1–1 reference column goes and why a ledger needs no
updated_*orversion.
Interview questions
Junior
How does an entity differ from a config table?
An entity has an identity and a lifecycle; a config table is rarely changing lookup data with no meaningful lifecycle.
Entities usually have a status that changes over time (orders, posts). Config tables (countries, currencies) suit natural keys and application-level caching.
How does a foreign key differ from a reference column?
The reference column is data (it stores the parent’s id); the foreign key is a database constraint on that column guaranteeing it points to an existing parent row.
Drop the constraint and the column stays, and still needs an index if queries follow it.
What primary key does a relation table for an N–M relationship usually have?
A composite key of the two foreign keys, e.g. post_tag (post_id, tag_id).
It prevents duplicate pairs and serves queries on the leading column. Add an index on (tag_id, post_id) if you query the other direction. Usually only created_at is added, not the full table template.
Mid
How do you start designing the schema for a new feature?
I write down the main queries and writes first, each with a frequency and a latency target.
Then I group fields into entities, classify the tables (entity, relation, support, config), normalize as the baseline, and check each hot query for an index that can serve it; where none can, I consider deliberate denormalization.
In a 1–1 relationship between an order and its payment transaction, which table holds the reference column?
The table created later, i.e. payment_txn.order_id, with UNIQUE to keep it 1–1.
When the order is created there is no transaction yet, so no insert-then-update. New transaction kinds (a donation with no order) don’t require changing orders.
Why is read-modify-write of a counter column in Java wrong?
Two requests read the same value, both add one and both write, so one increment is lost (a lost update).
Fix it with an atomic SET count = count + 1 in SQL, or optimistic locking with @Version so one side fails and retries.
When do you add a redundant column to a normalized table?
When a hot query filters on one table and sorts on another, so no composite index can serve it end to end.
Example: a feed by the author’s country. Adding author_country to post with an index on (author_country, created_at) cut 125,000 buffers to 23. Conditions: the source data rarely changes, there is one sync mechanism, and a reconciliation job.
Senior
Would you drop foreign key constraints?
By default I keep them when the tables share a database and are owned by one service: the check is one index lookup, and in exchange there are never orphan rows.
I drop them across shards or services, when I need a migration tool like gh-ost, or when InnoDB’s shared lock on the parent row causes measured contention. Then I add a locking check or an orphan-finding job; a non-locking SELECT then INSERT has a race condition.
What does inserting a child row with a foreign key do to the parent row?
InnoDB puts a shared lock on the parent record until the transaction ends, blocking every UPDATE of the parent row; PostgreSQL only takes FOR KEY SHARE.
FOR KEY SHARE blocks deletes and key changes but not updates of ordinary columns. With a hot parent row on MySQL, this is a source of lock waits and deadlocks.
Should a ledger table get the full table template?
No. A ledger is append-only: mistakes are fixed with a reversing entry, not an edit.
It only needs created_at and created_by; drop updated_* and version because they imply edits are allowed. Turn the convention into a constraint with a trigger blocking UPDATE and DELETE, and REVOKE those privileges from the application role.
Useful phrases for interviews
- “Before I draw a single table, I list the access patterns with their frequency and latency targets.”
- “This query filters on one table and sorts on another, so no index can serve it end to end; that’s where I’d denormalize one column.”
- “I treat the status column as a state machine and guard each transition in the UPDATE’s WHERE clause.”
- “By default I keep foreign keys inside one service’s database; I only drop them across shards or services, and then I add an orphan check.”
- “On MySQL, a child insert takes a shared lock on the parent row, so a hot parent becomes a contention point.”
- “The entity created later holds the reference, so new kinds of children never force a change to the original table.”
- “Every entity table gets audit columns and a version column for optimistic locking; append-only tables don’t need updated_at.”
- “With NoSQL there’s no ad hoc query to fall back on, so the access pattern list effectively is the schema.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.