Database Design Practice

Knowing normal forms, keys and indexes is not the same as designing a good schema under interview pressure. "Design the database for an e-commerce site" or "for Instagram" is one of the most common DBMS and low-level design questions, and interviewers watch how you get there: do you start from the queries, choose sensible keys, enforce rules with constraints, index for the real access paths, and handle concurrency where money or inventory is involved? This lesson gives you a repeatable method, then applies it to three complete designs: (a) e-commerce orders, (b) an Instagram-like social app with a feed, and (c) a booking system that makes double booking impossible. It finishes with the conventions that separate a production schema from a classroom one: naming, soft deletes, audit columns, enums versus lookup tables, JSON columns and migrations.

All DDL below was run in SQLite 3.51 with PRAGMA foreign_keys = ON; (SQLite does not enforce foreign keys without it). Where PostgreSQL or MySQL needs different syntax, it is labelled.

A repeatable schema-design method

Use these eight steps, in this order, every time. Say them out loud in an interview; it shows structure.

1. Clarify requirements and scale. What does the system do? Which features are in scope? Roughly how many users, rows per day, reads per write? A schema for 1,000 orders a day and one for 1,000 orders a second differ.
2. List the main queries and writes. Write them as sentences: "show a customer's last 20 orders", "decrement stock when an order is placed", "show the feed of posts from people I follow". The schema exists to serve these.
3. Identify entities and relationships. Nouns become tables (customer, product, order). For each relationship, decide its cardinality: one-to-one, one-to-many, many-to-many. Sketch an ER model if it helps.
4. Choose keys. Pick a primary key for every table (usually a surrogate key: an auto-generated integer or UUID). Add unique constraints for natural keys (email, SKU). A many-to-many relationship becomes a junction table whose primary key is the pair of foreign keys.
5. Define columns, types and constraints. Use the right types (integers for money in the smallest unit, timestamps for time). Add NOT NULL by default, CHECK constraints for valid values, and FOREIGN KEY constraints for every reference.
6. Normalise, then denormalise deliberately. Start in third normal form so each fact lives in one place. Then, only for a measured or clearly predictable read need, add controlled redundancy (a counter, a copied price) and say how you keep it correct.
7. Index for the queries from step 2. Each frequent query should be served by an index, usually a composite index with equality columns first and the sort column last. Index foreign keys used in joins. Do not index everything; every index slows writes.
8. Handle concurrency and lifecycle. Where can two requests race (stock, seats, likes)? Use constraints, conditional updates or locks. Then decide deletion (soft or hard), audit columns, and how the schema will change over time (migrations).

Design (a): e-commerce orders

Requirements and queries

Customers browse products, place orders containing several products, pay, and see their order history. The operations team lists orders by status.

Main paths:

• Q1. Show a customer's orders, newest first, 20 per page.
• Q2. Show one order with its items.
• Q3. Place an order: check and decrement stock, create the order and its items, all atomically.
• Q4. Operations: list pending orders, oldest first.
• Q5. Report: best-selling products.

Entities and relationships

  customers 1---* addresses
      |
      1
      |
      *
   orders 1---* order_items *---1 products
      |
      1
      |
      *
   payments

• A customer has many addresses and many orders.
• An order has many items; a product appears in many orders. Order-to-product is many-to-many, resolved by the junction table order_items.
• An order can have several payment attempts (a failed one, then a successful one).

DDL

PRAGMA foreign_keys = ON;   -- SQLite only

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  email       TEXT    NOT NULL UNIQUE,
  full_name   TEXT    NOT NULL,
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE addresses (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  line1        TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  pincode      TEXT    NOT NULL CHECK (length(pincode) = 6),
  is_default   INTEGER NOT NULL DEFAULT 0 CHECK (is_default IN (0, 1))
);
CREATE INDEX idx_addresses_customer ON addresses (customer_id);

CREATE TABLE products (
  id           INTEGER PRIMARY KEY,
  sku          TEXT    NOT NULL UNIQUE,
  name         TEXT    NOT NULL,
  price_paise  INTEGER NOT NULL CHECK (price_paise >= 0),
  stock        INTEGER NOT NULL CHECK (stock >= 0),
  status       TEXT    NOT NULL DEFAULT 'active'
               CHECK (status IN ('active', 'archived'))
);

CREATE TABLE orders (
  id                  INTEGER PRIMARY KEY,
  customer_id         INTEGER NOT NULL REFERENCES customers(id),
  shipping_address_id INTEGER NOT NULL REFERENCES addresses(id),
  status              TEXT    NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending', 'paid', 'shipped',
                                        'delivered', 'cancelled')),
  total_paise         INTEGER NOT NULL CHECK (total_paise >= 0),
  created_at          TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
CREATE INDEX idx_orders_status_created   ON orders (status, created_at);
CREATE INDEX idx_orders_address          ON orders (shipping_address_id);

CREATE TABLE order_items (
  order_id          INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id        INTEGER NOT NULL REFERENCES products(id),
  quantity          INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_paise  INTEGER NOT NULL CHECK (unit_price_paise >= 0),
  PRIMARY KEY (order_id, product_id)
);
CREATE INDEX idx_order_items_product ON order_items (product_id);

CREATE TABLE payments (
  id            INTEGER PRIMARY KEY,
  order_id      INTEGER NOT NULL REFERENCES orders(id),
  provider_ref  TEXT    NOT NULL UNIQUE,
  amount_paise  INTEGER NOT NULL CHECK (amount_paise > 0),
  status        TEXT    NOT NULL
                CHECK (status IN ('initiated', 'succeeded', 'failed', 'refunded')),
  created_at    TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_payments_order ON payments (order_id);

In PostgreSQL you would write id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, timestamptz for times, and boolean for is_default; in MySQL, BIGINT AUTO_INCREMENT PRIMARY KEY, DATETIME or TIMESTAMP, and BOOLEAN. MySQL enforces CHECK constraints only from 8.0.16; earlier versions parse and ignore them.

Design decisions, explained

• Money as integer paise. Floating-point types cannot represent 0.10 exactly, so sums drift. Store the smallest currency unit as an integer (or use NUMERIC(12,2) / DECIMAL(12,2), which are exact).
• unit_price_paise copied into order_items. This looks like redundancy, but it is a different fact: the price at the time of purchase. When the product price changes tomorrow, old orders must not change. The same reasoning applies to copying the shipping address into the order if addresses can be edited; here we reference an address row, so the application must never edit an address that orders use (create a new one instead). Saying which choice you made, and why, is what interviewers look for.
• total_paise stored on the order. It is derivable from the items, but it is stored because it is what the customer was charged, and it makes order lists cheap. The order and its items are written in one transaction, so they cannot disagree.
• Composite primary key (order_id, product_id) prevents the same product appearing twice in one order; increase quantity instead.
• status with a CHECK list keeps garbage values out. (Enums versus lookup tables are discussed below.)
• payments.provider_ref UNIQUE makes payment webhooks idempotent: if the payment provider sends the same notification twice, the second insert fails instead of recording a double payment.
• Indexes match the queries: (customer_id, created_at DESC) serves Q1 directly, (status, created_at) serves Q4, and order_items (product_id) supports Q5 and the foreign key.

Placing an order without overselling (Q3)

BEGIN;
-- conditional decrement: succeeds only if enough stock remains
UPDATE products SET stock = stock - 1 WHERE id = 100 AND stock >= 1;
-- application: if 0 rows changed, ROLLBACK and report "out of stock"

INSERT INTO orders (id, customer_id, shipping_address_id, total_paise)
VALUES (1000, 1, 10, 249900);
INSERT INTO order_items (order_id, product_id, quantity, unit_price_paise)
VALUES (1000, 100, 1, 249900);
COMMIT;

The WHERE stock >= 1 makes check and decrement a single atomic step, so two buyers of the last unit cannot both succeed: the second update finds stock = 0 and changes zero rows. The CHECK (stock >= 0) constraint is a second safety net. Tested: for a product with stock 0, the update reports changes() = 0.

If an order has several products, lock them in a consistent order (for example ascending product id) to avoid deadlocks between concurrent orders, as explained in concurrency control.

Sample queries

-- Q1: a customer's order history, newest first
SELECT o.id, o.status, o.total_paise, SUM(oi.quantity) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 1
GROUP BY o.id, o.status, o.total_paise, o.created_at
ORDER BY o.created_at DESC
LIMIT 20;

-- Q2: one order with its items
SELECT p.name, oi.quantity, oi.unit_price_paise,
       oi.quantity * oi.unit_price_paise AS line_total_paise
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 1000;

-- Q4: pending orders, oldest first
SELECT id, customer_id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

-- Q5: best sellers
SELECT p.sku, p.name,
       SUM(oi.quantity)                      AS units,
       SUM(oi.quantity * oi.unit_price_paise) AS revenue_paise
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.id, p.sku, p.name
ORDER BY units DESC
LIMIT 10;

Constraint tests from the SQLite run: inserting an item with quantity = 0 fails with "CHECK constraint failed: quantity > 0", an order with status = 'lost' fails its CHECK, and an order for customer 99 (who does not exist) fails with "FOREIGN KEY constraint failed". EXPLAIN QUERY PLAN for Q1's filter shows SEARCH orders USING COVERING INDEX idx_orders_customer_created (customer_id=?).

Design (b): an Instagram-like social app

Requirements and queries

Users post photos with captions, follow other users, like and comment on posts, and see a feed of recent posts from the people they follow.

Main paths:

• Q1. Home feed: newest posts by people I follow, 20 per page.
• Q2. Profile page: a user's posts, newest first; follower and following counts.
• Q3. Like / unlike a post; show like counts; show whether I liked a post.
• Q4. Comments on a post, oldest first.
• Q5. "People you may know": users followed by people I follow.

Reads vastly outnumber writes; a feed is loaded far more often than a post is created.

Entities and relationships

             follows (follower_id, followee_id)
           +--------------------------------+
           |                                |
           v                                v
        users 1-------------------------* posts
          |  \                             /  |
          |   \ 1                       1 /   | 1
          |    *        likes           *     |
          |     (user_id, post_id) many-to-many
          |                                   *
          +------------1-------------* comments

• Follows is a many-to-many relationship from users to users (a self-referencing junction table). It is directed: Asha following Ravi does not mean Ravi follows Asha.
• Likes is many-to-many between users and posts.
• Comments belong to one post and one author.

DDL

PRAGMA foreign_keys = ON;   -- SQLite only

CREATE TABLE users (
  id          INTEGER PRIMARY KEY,
  username    TEXT    NOT NULL UNIQUE,
  bio         TEXT,
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE follows (
  follower_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  followee_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id),
  CHECK (follower_id <> followee_id)
);
CREATE INDEX idx_follows_followee ON follows (followee_id, follower_id);

CREATE TABLE posts (
  id          INTEGER PRIMARY KEY,
  author_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  caption     TEXT,
  media_url   TEXT    NOT NULL,
  like_count  INTEGER NOT NULL DEFAULT 0 CHECK (like_count >= 0),
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at  TEXT
);
CREATE INDEX idx_posts_author_created ON posts (author_id, created_at DESC, id DESC);

CREATE TABLE likes (
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, user_id)
);
CREATE INDEX idx_likes_user ON likes (user_id, created_at DESC);

CREATE TABLE comments (
  id          INTEGER PRIMARY KEY,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body        TEXT    NOT NULL CHECK (length(body) BETWEEN 1 AND 2200),
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_comments_post_created ON comments (post_id, created_at);
CREATE INDEX idx_comments_author ON comments (author_id);

Design decisions, explained

• follows primary key (follower_id, followee_id) prevents duplicate follows and serves "whom do I follow?" (leading column follower_id). The second index (followee_id, follower_id) serves "who follows me?". A junction table usually needs an index in both directions.
• CHECK (follower_id <> followee_id) stops self-follows.
• likes primary key (post_id, user_id) makes a double like impossible (tested: the second insert fails with "UNIQUE constraint failed"). It also serves "who liked this post?" and "did I like this post?". idx_likes_user serves "posts I liked".
• like_count is a denormalised counter. Counting likes with COUNT(*) for every post in every feed would be expensive for posts with millions of likes. The counter is updated in the same transaction as the like row:

BEGIN;
INSERT INTO likes (user_id, post_id) VALUES (1, 13);
UPDATE posts SET like_count = like_count + 1 WHERE id = 13;
COMMIT;

If the insert fails (already liked), the transaction is rolled back and the counter is untouched. At very high scale, a single hot counter row becomes a contention point; then you shard the counter or aggregate likes asynchronously and accept a slightly stale count.

• posts (author_id, created_at DESC, id DESC) serves the profile page and the feed's per-author lookups; id breaks ties for stable pagination.
• deleted_at on posts is a soft delete (see below), so a removed post disappears from feeds but can be restored or kept for moderation.

The feed query (Q1)

The simplest feed is fan-out on read (also called pull): when a user opens the app, join their follows with recent posts.

-- page 1 for user 1
SELECT p.id, u.username, p.caption, p.created_at, p.like_count
FROM follows f
JOIN posts p ON p.author_id = f.followee_id
JOIN users u ON u.id = p.author_id
WHERE f.follower_id = 1
  AND p.deleted_at IS NULL
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

-- next page: keyset pagination from the last row seen
SELECT p.id, u.username, p.caption, p.created_at, p.like_count
FROM follows f
JOIN posts p ON p.author_id = f.followee_id
JOIN users u ON u.id = p.author_id
WHERE f.follower_id = 1
  AND p.deleted_at IS NULL
  AND (p.created_at, p.id) < ('2026-10-10 07:15:00', 14)
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

With the sample data (Asha follows Ravi and Meera; Kiran is not followed), page 1 with LIMIT 2 returned posts 13 (Ravi, "Sunset") and 14 (Meera, "Coffee"); page 2 returned 11 and 10; Kiran's post 12 never appeared. The plan used the follows primary key, then idx_posts_author_created for each followee.

Fan-out on read works well until a user follows thousands of accounts, at which point every feed load merges thousands of index ranges. Large systems switch to fan-out on write (push): when someone posts, insert the post id into a precomputed feed_items (user_id, created_at, post_id) table (or a cache) for each follower, so reading a feed is one index range scan. Accounts with millions of followers are usually handled with pull to avoid millions of inserts per post: a hybrid approach. This is covered in design a Twitter timeline.

-- fan-out-on-write table (the push model)
CREATE TABLE feed_items (
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at  TEXT    NOT NULL,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, created_at, post_id)
);

Other sample queries

-- Q2: follower and following counts
SELECT (SELECT COUNT(*) FROM follows WHERE followee_id = 2) AS followers,
       (SELECT COUNT(*) FROM follows WHERE follower_id = 2) AS following;

-- Q3: did user 1 like these posts?
SELECT p.id,
       EXISTS (SELECT 1 FROM likes l
               WHERE l.post_id = p.id AND l.user_id = 1) AS liked_by_me
FROM posts p
WHERE p.id IN (13, 14);

-- Q5: people you may know (followed by people I follow)
SELECT f2.followee_id AS suggested, COUNT(*) AS mutual
FROM follows f1
JOIN follows f2 ON f2.follower_id = f1.followee_id
WHERE f1.follower_id = 1
  AND f2.followee_id <> 1
  AND NOT EXISTS (SELECT 1 FROM follows x
                  WHERE x.follower_id = 1
                    AND x.followee_id = f2.followee_id)
GROUP BY f2.followee_id
ORDER BY mutual DESC
LIMIT 10;

On the sample data (Asha follows Ravi and Meera, both of whom follow Kiran), Q5 returns Kiran with 2 mutual connections. For celebrity accounts, follower counts would also be stored as denormalised counters rather than counted each time.

Design (c): a booking system with no double booking

Requirements and queries

A hotel booking site: guests search for available rooms for a date range and book one. Two guests must never hold the same room for the same night, even if they click "Book" at the same millisecond.

Main paths:

• Q1. Search: which rooms in hotel H are free from date X to date Y?
• Q2. Book room R from X to Y, atomically, without double booking.
• Q3. Cancel a booking and free the nights.
• Q4. A guest's bookings.

Why this is hard

The naive approach is "check, then insert":

  Request A                         Request B
  --------------------------------  --------------------------------
  SELECT overlapping bookings
   for room 1, 13-15 Oct -> none
                                    SELECT overlapping bookings
                                     for room 1, 13-15 Oct -> none
  INSERT booking (room 1, 13-15)
                                    INSERT booking (room 1, 13-15)
  -> two bookings for the same room and nights

This is the phantom / write skew problem from the concurrency control lesson: both checks see no conflicting row because the conflicting row does not exist yet, so there is nothing to lock. A row lock on existing bookings cannot help. The reliable fixes all make the database enforce the rule.

Approach 1: one row per room per night (portable)

Turn the overlap rule into a uniqueness rule. A stay from 12 to 14 October occupies the nights of the 12th and the 13th (the guest leaves on the 14th). Store one row per occupied night, with a primary key on (room_id, night). Two bookings that overlap must share at least one night, so the second insert violates the primary key, regardless of timing.

PRAGMA foreign_keys = ON;   -- SQLite only

CREATE TABLE rooms (
  id          INTEGER PRIMARY KEY,
  hotel_id    INTEGER NOT NULL,
  room_number TEXT    NOT NULL,
  capacity    INTEGER NOT NULL CHECK (capacity BETWEEN 1 AND 10),
  UNIQUE (hotel_id, room_number)
);

CREATE TABLE bookings (
  id          INTEGER PRIMARY KEY,
  room_id     INTEGER NOT NULL REFERENCES rooms(id),
  guest_id    INTEGER NOT NULL,
  check_in    TEXT    NOT NULL,
  check_out   TEXT    NOT NULL,
  status      TEXT    NOT NULL DEFAULT 'confirmed'
              CHECK (status IN ('confirmed', 'cancelled')),
  created_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (check_out > check_in)
);
CREATE INDEX idx_bookings_room_dates ON bookings (room_id, check_in, check_out);
CREATE INDEX idx_bookings_guest      ON bookings (guest_id, check_in);

CREATE TABLE room_nights (
  room_id     INTEGER NOT NULL REFERENCES rooms(id),
  night       TEXT    NOT NULL,
  booking_id  INTEGER NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  PRIMARY KEY (room_id, night)
);
CREATE INDEX idx_room_nights_booking ON room_nights (booking_id);

Booking (Q2) inserts the booking and its nights in one transaction:

-- Asha: room 1, check-in 12 Oct, check-out 14 Oct
BEGIN;
INSERT INTO bookings (id, room_id, guest_id, check_in, check_out)
VALUES (500, 1, 42, '2026-10-12', '2026-10-14');
INSERT INTO room_nights (room_id, night, booking_id) VALUES
  (1, '2026-10-12', 500),
  (1, '2026-10-13', 500);
COMMIT;

-- Ravi: room 1, 13 Oct to 15 Oct -> overlaps the night of the 13th
BEGIN;
INSERT INTO bookings (id, room_id, guest_id, check_in, check_out)
VALUES (501, 1, 43, '2026-10-13', '2026-10-15');
INSERT INTO room_nights (room_id, night, booking_id) VALUES
  (1, '2026-10-13', 501),   -- fails: UNIQUE constraint failed
  (1, '2026-10-14', 501);
ROLLBACK;                   -- application rolls back on the error

Tested in SQLite: Ravi's night insert fails with "UNIQUE constraint failed: room_nights.room_id, room_nights.night", the rollback leaves no booking 501, and a booking for 14 to 16 October then succeeds, because the check-out day is not an occupied night.

Search (Q1) uses the standard interval overlap test. Two ranges a_start, a_end) and [b_start, b_end) overlap exactly when a_start < b_end AND b_start < a_end:

-- rooms in hotel 7 free from 13 Oct (check-in) to 15 Oct (check-out)
SELECT r.id, r.room_number
FROM rooms r
WHERE r.hotel_id = 7
  AND NOT EXISTS (
    SELECT 1 FROM bookings b
    WHERE b.room_id = r.id
      AND b.status = 'confirmed'
      AND b.check_in  < '2026-10-15'
      AND b.check_out > '2026-10-13'
  );

Search is only advisory; the unique key is what guarantees correctness when two people book the room shown as free.

Cancelling (Q3) marks the booking and deletes its nights so they can be rebooked:

BEGIN;
UPDATE bookings SET status = 'cancelled' WHERE id = 500;
DELETE FROM room_nights WHERE booking_id = 500;
COMMIT;

Trade-off: a 30-night stay writes 30 rows. That is fine for hotels (stays are short and bounded); for minute-level resources such as meeting rooms, use approach 2.

Approach 2: an exclusion constraint (PostgreSQL only)

PostgreSQL can enforce "no two rows overlap" directly with an exclusion constraint on a range type:

-- PostgreSQL only
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- lets GiST index plain = on room_id

CREATE TABLE bookings (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id   bigint    NOT NULL REFERENCES rooms(id),
  guest_id  bigint    NOT NULL,
  stay      daterange NOT NULL,          -- e.g. '[2026-10-12,2026-10-14)'
  status    text      NOT NULL DEFAULT 'confirmed'
            CHECK (status IN ('confirmed', 'cancelled')),
  EXCLUDE USING gist (room_id WITH =, stay WITH &&)
    WHERE (status = 'confirmed')
);

The constraint says: no two confirmed rows may have equal room_id and overlapping (&&) stay ranges. A daterange is half-open ([)) by default, so a stay ending on the 14th does not clash with one starting on the 14th. A concurrent conflicting insert fails with a "conflicting key value violates exclusion constraint" error. This handles arbitrary time ranges in one row per booking. MySQL and SQLite have no equivalent.

Approach 3: lock a parent row (any database with row locks)

Serialise all bookings for one room by locking the room row first:

-- PostgreSQL or MySQL
BEGIN;
SELECT id FROM rooms WHERE id = 1 FOR UPDATE;   -- one booker per room at a time
SELECT 1 FROM bookings
 WHERE room_id = 1 AND status = 'confirmed'
   AND check_in < '2026-10-15' AND check_out > '2026-10-13';
-- if no row: insert the booking
INSERT INTO bookings (room_id, guest_id, check_in, check_out)
VALUES (1, 43, '2026-10-13', '2026-10-15');
COMMIT;

Because the second request blocks on the room row until the first commits, its overlap check sees the first booking. It works, but every code path that books must remember to take the lock, whereas a constraint protects against every path, including manual SQL and future code.

Seat-based variant: holds that expire

For cinemas and flights, the inventory is a fixed set of seats per show, so you can create one row per seat up front and claim it with a conditional update, usually with a temporary hold while the user pays:

CREATE TABLE show_seats (
  show_id     INTEGER NOT NULL,
  seat_id     INTEGER NOT NULL,
  status      TEXT    NOT NULL DEFAULT 'available'
              CHECK (status IN ('available', 'held', 'booked')),
  held_by     INTEGER,
  held_until  TEXT,
  PRIMARY KEY (show_id, seat_id)
);

-- claim seat 1 for 10 minutes, only if it is free or its hold expired
UPDATE show_seats
   SET status = 'held', held_by = 42,
       held_until = datetime('now', '+10 minutes')     -- SQLite syntax
 WHERE show_id = 9 AND seat_id = 1
   AND (status = 'available'
        OR (status = 'held' AND held_until < datetime('now')));
-- 1 row changed: you have the seat. 0 rows: someone else does.

Tested in SQLite: the first claim changed 1 row and an immediate second claim by another user changed 0 rows. (In PostgreSQL use now() + interval '10 minutes'; in MySQL NOW() + INTERVAL 10 MINUTE.) The [movie ticket booking LLD lesson builds the full flow around this.

Naming conventions

Consistency matters more than any particular style. A widely used set:

Avoid reserved words as names (order, user, group): they need quoting everywhere. PostgreSQL folds unquoted identifiers to lower case, so CamelCase names force quotes forever; stick to lower-case snake case. Store units in names when they are not obvious (price_paise, duration_ms).

Soft deletes

A soft delete marks a row as deleted instead of removing it, usually with a nullable deleted_at timestamp (better than a boolean, because it records when).

Benefits: undo ("restore post"), audit and moderation history, and referential safety (old orders still point at a "deleted" product).

Costs and pitfalls:

• Every query must filter WHERE deleted_at IS NULL. Forgetting it once leaks deleted data. Mitigate with a view (CREATE VIEW active_posts AS SELECT ... WHERE deleted_at IS NULL), ORM default scopes, or PostgreSQL row-level security.
• Unique constraints break: a user deletes their account, then cannot re-register with the same email because the soft-deleted row still holds it. Fix with a partial unique index (PostgreSQL and SQLite): CREATE UNIQUE INDEX uq_users_email_active ON users (email) WHERE deleted_at IS NULL;. MySQL has no partial indexes; a common workaround is a generated column that is NULL for deleted rows, with a unique index on it.
• Tables grow forever: archive or hard-delete old soft-deleted rows on a schedule.
• Privacy law: regulations such as India's DPDP Act or the EU's GDPR give users rights to have personal data erased. Soft-deleted personal data may need real deletion or anonymisation.

Use soft deletes for user-facing content that may need restoring; use hard deletes (with ON DELETE CASCADE where appropriate) for data that has no value once removed, such as sessions.

Audit columns

Almost every table benefits from:

• created_at: when the row was inserted (DEFAULT CURRENT_TIMESTAMP).
• updated_at: when it last changed. The database does not maintain this automatically in PostgreSQL or SQLite; use a trigger or set it in application code. MySQL can: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
• Optionally created_by / updated_by: which user or service made the change.

Store timestamps in UTC (timestamptz in PostgreSQL stores an absolute instant) and convert to local time (IST, for example) only for display.

For a full history (who changed the price from what to what), audit columns are not enough; add an audit log table written by triggers or application code, or use change data capture (CDC) from the database log, discussed in transactions and CDC.

-- an audit trail for price changes, written by a trigger (SQLite syntax)
CREATE TABLE product_price_history (
  id           INTEGER PRIMARY KEY,
  product_id   INTEGER NOT NULL REFERENCES products(id),
  old_paise    INTEGER NOT NULL,
  new_paise    INTEGER NOT NULL,
  changed_at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER trg_products_price_audit
AFTER UPDATE OF price_paise ON products
WHEN OLD.price_paise <> NEW.price_paise
BEGIN
  INSERT INTO product_price_history (product_id, old_paise, new_paise)
  VALUES (OLD.id, OLD.price_paise, NEW.price_paise);
END;

Tested: changing a product's price from 249900 to 229900 paise wrote one history row; repeating the same update wrote none, thanks to the WHEN clause.

Enums versus lookup tables

A status column with a small fixed set of values can be modelled three ways:

Rules of thumb: use a CHECK constraint (or enum) for small, stable sets controlled by developers (order status). Use a lookup table when values change at run time, are managed by admins, or carry extra data (countries, product categories, plan tiers with prices). Prefer a status column with several values over several booleans (is_paid, is_shipped, is_cancelled can contradict each other).

JSON columns: when they are fine

PostgreSQL (JSONB), MySQL (JSON) and SQLite (JSON functions on text) can store JSON in a column. Used well, this is useful; used as a way to avoid design, it hurts.

Good uses:

• Data you store and return as a whole but rarely filter on: raw payment-provider webhook payloads, third-party API responses, user interface preferences.
• Sparse attributes that vary by type: a product catalogue where phones have ram_gb and shirts have size. (A common pattern: real columns for shared, filtered fields; a JSON column for the long tail.)
• Event or audit payloads.

Bad uses:

• Fields you filter, join, sort or aggregate on regularly (move them to real columns with indexes; PostgreSQL can index JSONB with GIN or expression indexes, but plain columns are simpler and better understood by the optimiser).
• Data with relationships (foreign keys cannot point into JSON).
• Anything needing constraints (a CHECK on a JSON path is possible but clumsy).

-- SQLite (PostgreSQL would use a JSONB column and attrs->>'ram_gb')
CREATE TABLE products_v2 (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  attrs  TEXT NOT NULL DEFAULT '{}' CHECK (json_valid(attrs))
);
INSERT INTO products_v2 VALUES
  (1, 'Phone A', '{"ram_gb": 8, "storage_gb": 128}'),
  (2, 'Shirt',   '{"size": "M"}'),
  (3, 'Phone B', '{"ram_gb": 6}');

SELECT id, name FROM products_v2
WHERE json_extract(attrs, '$.ram_gb') >= 8;   -- returns 1 | Phone A

Migrations

A migration is a versioned script that changes the schema (and sometimes data) from one version to the next. Tools: Flyway and Liquibase (Java), Alembic (Python/SQLAlchemy), Django migrations, Rails Active Record migrations, Prisma Migrate, golang-migrate.

Good practices:

• Every schema change is a migration file in version control, applied in order, never edited after it has run in production.
• Make migrations idempotent where possible (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS), so a partly applied run can be retried.
• Use transactions where DDL is transactional (PostgreSQL, SQLite). In MySQL, DDL commits implicitly, so a failure mid-file leaves a partial change; keep MySQL migrations small.
• Expand, then contract for zero-downtime changes. To rename a column: (1) add the new column, (2) deploy code that writes both, (3) backfill old rows in batches, (4) switch reads to the new column, (5) drop the old column in a later release. Never rename or drop in the same deploy that changes code reading it.
• Beware of locking DDL on big tables: adding an index can block writes. Use CREATE INDEX CONCURRENTLY (PostgreSQL) or online DDL (ALGORITHM=INPLACE, LOCK=NONE in MySQL 5.6+), and add NOT NULL columns with a default carefully (cheap in PostgreSQL 11+ and MySQL 8.0 INSTANT, a full rewrite in older versions).
• Backfill in batches (for example 1,000 rows per transaction) rather than one huge UPDATE that holds locks and bloats the log.

Interview questions

Q1. How do you approach a schema design question?

Clarify requirements and scale, list the main reads and writes, identify entities and relationship cardinalities, choose primary and unique keys, define columns with types and constraints, normalise to 3NF and then denormalise only where a read path justifies it, add indexes for each main query, and finally handle concurrency, deletion and migrations. Starting from queries keeps the design practical.

Q2. How do you model a many-to-many relationship?

With a junction table holding the two foreign keys, whose primary key is the pair, for example order_items (order_id, product_id) or follows (follower_id, followee_id). Add a second index on the reversed pair if you query from the other side. The junction table can carry attributes of the relationship, such as quantity or the time a follow happened.

Q3. Why copy the product price into order items?

Because it is a different fact: the price at the time of purchase. If you joined to the current product price, old orders and invoices would change whenever the price changes. This is a deliberate, correct redundancy, not a normalisation violation.

Q4. How do you prevent overselling the last item in stock?

Use a single conditional update, UPDATE products SET stock = stock - 1 WHERE id = ? AND stock >= 1, and check that one row changed, inside the order's transaction. Back it with CHECK (stock >= 0). Alternatively lock the row with SELECT ... FOR UPDATE before checking. A separate read and write without a lock is a race.

Q5. How would you guarantee no double booking?

Make the database enforce it. Store one row per room per night with a primary key on (room_id, night), use a PostgreSQL exclusion constraint on (room_id WITH =, stay WITH &&), or for seats claim pre-created seat rows with a conditional update. A check-then-insert is unsafe because the conflicting row does not exist to be locked, a phantom problem.

Q6. Fan-out on read versus fan-out on write for a feed?

Fan-out on read builds the feed at request time by joining follows with recent posts; writes are cheap and reads are expensive. Fan-out on write pushes each new post into every follower's precomputed feed; reads are a single index scan but a celebrity's post means millions of writes. Large systems use a hybrid: push for normal accounts, pull for celebrity accounts.

Q7. How do you store like counts?

Keep a likes (post_id, user_id) table with a composite primary key to prevent double likes, and a denormalised like_count on the post updated in the same transaction. At very large scale the counter row becomes contended, so it can be sharded or updated asynchronously from the likes table, accepting a slightly stale count.

Q8. What are soft deletes and their drawbacks?

A soft delete sets a deleted_at column instead of removing the row, allowing restore and history. Drawbacks: every query must filter deleted rows, unique constraints must become partial indexes, tables keep growing, and personal data may legally need real deletion. Views or ORM scopes reduce the risk of forgetting the filter.

Q9. Enum or lookup table?

Use a CHECK constraint or enum for a small, stable set of values controlled by developers, such as an order status. Use a lookup table when values change at run time, are managed by non-developers, or carry attributes such as display labels or prices. Native enums are hard to modify later, especially to remove values.

Q10. When is a JSON column appropriate?

For data stored and returned as a whole and rarely filtered, such as webhook payloads, preferences or sparse type-specific attributes. Not for fields you filter, join or aggregate on, or that need foreign keys and constraints; those belong in real columns. A hybrid of real columns for common fields and JSON for the long tail works well.

Q11. How do you rename a column with zero downtime?

Use expand and contract: add the new column, deploy code that writes to both, backfill old rows in batches, switch reads to the new column, and drop the old one in a later release. Each step is backward compatible with the code running at that time, so there is no moment where running code references a missing column.

Q12. Why store money as integers?

Binary floating-point cannot represent most decimal fractions exactly, so repeated arithmetic produces rounding errors. Storing the smallest unit (paise, cents) as an integer, or using an exact NUMERIC/DECIMAL type, keeps sums exact. Also store the currency if more than one is possible.

Q13. Which columns should you index?

Columns used in frequent WHERE, JOIN and ORDER BY clauses, in composite indexes ordered as equality columns first and then the range or sort column; foreign keys used in joins or cascades; and unique business keys. Avoid indexing low-selectivity columns alone and avoid unused indexes, since each index costs write time and space.

Q14. What audit information should tables carry?

At least created_at and updated_at, in UTC, and often created_by/updated_by. For full change history, add an audit log table filled by triggers or the application, or stream changes with CDC. updated_at needs a trigger or application code in PostgreSQL and SQLite; MySQL can maintain it with ON UPDATE CURRENT_TIMESTAMP.

Key takeaways

• Follow a method: requirements, queries, entities, keys, columns and constraints, normalise then denormalise deliberately, index for queries, then concurrency and lifecycle.
• Model many-to-many with junction tables keyed by the pair, indexed in both directions if queried both ways.
• Snapshot facts that must not change (price at purchase) and store money as integers or exact decimals.
• Prevent races with constraints and conditional updates: WHERE stock >= 1, unique (room_id, night), PostgreSQL exclusion constraints, seat holds with expiry.
• Feeds: fan-out on read is simple; fan-out on write scales reads; hybrids handle celebrities.
• Use consistent snake_case names, created_at/updated_at in UTC, and deleted_at soft deletes with partial unique indexes.
• Prefer CHECK or enums for stable small sets, lookup tables for managed data; use JSON only for unfiltered or sparse data.
• Ship schema changes as versioned, idempotent migrations using expand-and-contract and online index builds.

Next lesson

Continue with DBMS interview questions.