Domain Database Designs
Complete schemas with keys, indexes, transactions and scaling for inventory, payments and double-entry ledgers, e-commerce, social feeds, chat, booking, ride sharing and notification platforms.
Each topic is a worked design you can reproduce in an interview: entities, schema, the critical transaction, indexes from access patterns, and how it scales and fails.
Inventory: Stock, Reservations and Overselling Prevention
Inventory tracks stock per product per warehouse, reservations held during checkout, and an append-only stock movement ledger. Overselling is prevented with conditional atomic decrements or row locks, and reservations expire if payment doesn't complete. Distributed inventory adds per-warehouse allocation and eventual consistency for availability display.
8 min 1 code practice
Payments and Banking: Double-Entry Ledgers
Money systems record every movement as immutable double-entry ledger entries where debits equal credits per transaction; balances are derived (or maintained with strict constraints). Payment intents track the lifecycle with idempotency keys, and settlement and reconciliation compare internal records with the processor and bank files daily.
9 min 1 code practice
E-Commerce: Catalogue, Cart, Orders, Payments and Shipping
An e-commerce schema separates the catalogue (products, variants, categories, prices), carts, orders with immutable order lines capturing price at purchase, payments, shipments, reviews and coupons. Orders follow a state machine; the order-placement transaction reserves stock and records an outbox event; the catalogue is read-heavy and served by caches and search.
9 min 1 diagram 1 code practice
Social Media: Follows, Posts, Likes and Feeds
Social schemas store users, a follow graph, posts, likes and comments, with feeds built by fan-out on write (precompute each follower's timeline), fan-out on read (merge followees' posts at request time), or a hybrid that treats celebrities differently. Counters are denormalised and eventually consistent; media lives in object storage.
9 min 2 code practice
Chat: Conversations, Messages, Delivery and Read State
Chat stores conversations, participants and messages ordered per conversation, plus per-user delivery and read markers (not per-message read rows). Messages are append-heavy and read by conversation and recency, which suits partitioned tables or wide-column stores keyed by conversation and time bucket. Offline delivery uses per-user inboxes, and retention policies bound storage.
8 min 1 code practice
Booking: Availability, Holds and Double-Booking Prevention
Booking systems (hotels, flights, cinemas, events) model inventory per time slot or seat, place short holds during checkout, confirm on payment, and must never double-book. Seats use unique constraints per (event, seat); date ranges use exclusion constraints or per-night inventory rows with conditional decrements. Holds expire automatically.
9 min 1 code practice
Ride Sharing: Locations, Matching and Trips
Ride sharing separates fast-changing ephemeral data (driver locations every few seconds, kept in memory with geospatial indexes) from durable business data (riders, drivers, vehicles, trips, pricing, payments, ratings in a relational store). Matching queries nearby available drivers from the geo index; the trip record follows a state machine with an assignment that must be exclusive.
9 min 2 code practice
Notification Platform: Preferences, Templates, Delivery and Retries
A notification platform stores user preferences and quiet hours, templates with versions and locales, notifications and per-channel delivery attempts with provider responses. Sending is asynchronous with retries and backoff, deduplication, suppression lists and rate limits, and scheduled sends use indexed due-time queues.
8 min 1 code practice