Skip to content
notes.vinhnq.com

Search

Database & ORMSenior23 min read

The data modeling process: access patterns, table types, foreign keys and table templates

Review 16 cards
On this page

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:

  1. Analysis: requirements, the access pattern list, the existing schema (when changing a legacy system), a rough field list.
  2. Modeling: group fields into tables, refine, choose keys and relationships.
  3. 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:

  1. Session A runs BEGIN, sees that customer 2 exists, sleeps 2 seconds.
  2. Meanwhile session B deletes customer 2 and commits.
  3. 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_id in orders forces 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; store birth_date instead, because age changes every year.
  • MySQL has ON UPDATE CURRENT_TIMESTAMP(3) for updated_at; PostgreSQL needs a BEFORE UPDATE trigger (exercise 1) or the ORM.
  • Append-only tables (ledgers, event logs) need neither updated_at nor version; relation tables only need created_at.
  • The data JSON 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’s UPDATE ... SET status = 'PAID', version = version + 1 ... WHERE booking_id = 1 AND version = 0 returns 1 row.
  • A stale request with the same version = 0 condition returns 0 rows (the lost update is detected); updated_at is now later than created_at thanks to the trigger.
  • UPDATE entry is rejected with entry is append-only, UPDATE is not allowed.

Each decision:

  • flight (DDL as in the original table plus version, created_*, updated_* and the touch_updated_at trigger, not repeated here): the full set of technical columns, because flight times are edited by operations staff and by the schedule sync job; updated_by answers “who changed the time of VN213”. No data column: flight attributes are stable and all need filtering.
  • booking: the full set, and data jsonb fits sales-channel details (agent code, UTM, device) that are sparse and never filtered. version is mandatory because several parties modify a booking (customer, payment service, expiry job). created_by looks like a duplicate of user_id but isn’t: a call-center agent can create a booking on a customer’s behalf.
  • entry: only created_at and created_by. A ledger is never edited: mistakes are fixed with a reversing entry. updated_* or version would imply edits are allowed, so they go; the trigger blocking UPDATE and DELETE turns the convention into a constraint. In production, also REVOKE UPDATE, DELETE from 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_* or version.

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.