Schema Design: Normalise or Denormalise

Entities

Step one: find the entities. Read the product description and list the nouns. Users write posts, follow other users, and like posts. User, post, follow, like. Each noun becomes a table, and each table gets a primary key: a column that identifies one row, usually an id.

Here are users and posts. The important column is user_id in posts. It holds the id of the user who wrote the post. That's a foreign key: a column that points at a row in another table. One user, many posts. That's a one-to-many relationship, and it always lives as a foreign key on the many side.

users
  id          bigint PK
  name        text
  email       text UNIQUE

posts
  id          bigint PK
  user_id     bigint FK → users.id
  body        text
  created_at  timestamp

Many-to-many

Following is different. A user follows many users, and is followed by many users. Many-to-many. You cannot store that as one column, because a column holds one value. So you add a third table, a join table, with one row per edge: this follower follows this followee. The primary key is the pair, so the same follow can't be stored twice. Likes are the same shape: user id, post id.

With those tables, the home feed is one query. Take the people I follow, join to their posts, sort by time, take fifty. The join is where relationships pay off: nothing is duplicated, so a post exists in exactly one place and a user's name exists in exactly one place.

follows
  follower_id  bigint FK → users.id
  followee_id  bigint FK → users.id
  created_at   timestamp
  PRIMARY KEY (follower_id, followee_id)

one row per follow edge

SELECT p.*
FROM posts p
JOIN follows f
  ON f.followee_id = p.user_id
WHERE f.follower_id = :me
ORDER BY p.created_at DESC
LIMIT 50;

Normalise

That property has a name: normalised. Every fact is stored once. If Priya changes her name, that's one update, and every post shows the new name because posts only store her id. You never end up with two copies that disagree. When people say third normal form, this is what they mean in practice: no repeated facts, and every column depends on the key.

The cost

The cost shows up at scale. Every feed request joins follows to posts to users, then sorts. With a thousand follows and millions of posts, that's real work, on every refresh, for every user. Reads are the common case here, by a hundred to one, and the normalised schema makes the common case the expensive one.

Denormalise

So you denormalise on purpose. When Priya posts, write one row into the feed of each of her followers: feed items, user id, post id, time. That's fan-out on write. Now reading my feed is a single range scan on my user id, no joins at all. You stored the answer instead of recomputing the question.

Nothing is free. One post is now N writes, and for a celebrity with ten million followers that's a problem you have to design around, usually by not fanning out for them. The data is copied, so deleting a post means deleting every copy. You've traded write cost and some consistency work for fast reads. That trade is the whole point, and you should say it out loud.

The same move at smaller scale: counters. Don't count likes with a query every time a post is shown; keep a like count column and increment it. Copy the author's name onto hot rows if the join hurts. The rule: denormalise what you read a thousand times more than you write, and keep the normalised source as the truth you can rebuild from.

feed_items
  user_id     bigint   -- whose feed
  post_id     bigint
  created_at  timestamp
  PK (user_id, created_at, post_id)

read: WHERE user_id = :me
      ORDER BY created_at DESC LIMIT 50
no joins

posts.like_count     int  -- counter
  not COUNT(*) FROM likes each time

posts.author_name    text -- copy
  avoid the users join on hot paths

users.follower_count int  -- counter

rule: denormalise what you read
      1000x more than you write

Details

A few details that signal experience. Primary keys: a bigint sequence, or a time-ordered UUID like version seven; random UUIDs scatter your index and hurt writes. Say what happens on delete: cascade, set null, or restrict. Timestamps in UTC with a timezone type. Money as integer minor units, never float. Status as an enum.

NoSQL

If the database is NoSQL, flip the order. You start from the queries, not the nouns. In a document database, embed what's read together, like a post with its author's name and its comments, and reference what isn't. In DynamoDB or Cassandra, you design one table per access pattern, keyed by exactly how you'll read it. Denormalised by default, because there are no joins to fall back on.

Method

Here's the method for the interview. One: nouns become tables, each with a primary key. Two: relationships become foreign keys; many-to-many gets a join table. Three: write down the three hottest queries. Four: index what they filter and sort on. Five: denormalise only the query that is still too slow, and name what it costs. In that order, every time.

In the interview

Say it like this. Users, posts, follows, likes; follows and likes are join tables. I'd keep it normalised first, so a name change is one update. The home feed is the hot read, so I'd precompute it into feed items on write, and skip the fan-out for accounts over a million followers. Four sentences, and you've shown the whole trade-off.