LLD Schema Design Lab

Map each first-class passenger to exactly one private cabin aboard Titanic. Design the 1:1 relationship between passengers and cabins.

Organize all 885 Titanic crew members into their proper departments. Design the 1:Many relationship between departments and crew.

Track the complete booking lifecycle - from initial purchase through upgrades, cancellations, and refunds - with proper financial precision.

Design the grand first-class dining reservation system - passengers, tables, sittings, and seat assignments in an elegant Many-to-Many schema.

The cargo manifest is riddled with data quality nightmares - comma-separated values, repeating groups, and atomic violations. Fix the 1NF mess.

The Titanic's mail room tracking system has redundant data scattered everywhere. Eliminate the partial dependencies to achieve Second Normal Form.

Design the lifeboat allocation system with CHECK constraints, capacity limits, and priority boarding rules enforced at the schema level. It also links to the passengers table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Build an immutable communication log with soft deletes and a tamper-proof audit trail for every Marconi wireless message sent or received.

Design the ultimate integration challenge - link survivors to passengers, crew, lifeboats, and medical records across every table built in Act 1. It also links to tables from earlier levels (passengers, crew and lifeboats) - add each as a stand-in with just an id so those links can be drawn.

Design the foundational user profile table for a campus social network - unique emails, house affiliations, and proper indexing for fast lookups.

Model bidirectional friendships using a self-referencing Many-to-Many pattern with canonical ordering to prevent duplicate rows.

Build a polymorphic content system - text posts, photo posts, and link shares - each with different metadata, plus likes and comments. It also links to the users table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a pre-computed feed table that denormalizes data for read performance - the classic fan-out-on-write pattern.

Model nested photo albums as a tree structure using self-referencing parent_id and materialized paths for efficient subtree queries. It also links to the users table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the events system with complex M:M RSVP metadata, recurring event patterns, and invitation tracking. It also links to the users table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design granular privacy controls - visibility enums, custom audience lists, and a blocked users system for row-level security modeling. It also links to the users table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Build the complete notification system - type registry, user preferences, read/unread tracking, and aggregation grouping for the Act 2 capstone. It also links to the users table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a flexible Role-Based Access Control system where permissions are data-driven, not hardcoded - roles, permissions, and a mapping between them.

Design financial data tables with DECIMAL precision - clients, accounts, stocks, and portfolio holdings where rounding errors are unacceptable. It also links to the brokers table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a trade execution system with double-entry bookkeeping - every buy creates a debit and credit, and the ledger must ALWAYS balance. It also links to tables from earlier levels (accounts and stocks) - add each as a stand-in with just an id so those links can be drawn.

Design tiered commission calculations stored as configurable data - rates that change over time, splits between broker and firm, and payout tracking. It also links to tables from earlier levels (roles, trades and brokers) - add each as a stand-in with just an id so those links can be drawn.

Model a state machine in the database - defined states, valid transitions between them, and a document management workflow for KYC compliance. It also links to the clients table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Build a tamper-proof, append-only audit log with hash-chain integrity - every action is recorded, nothing can be modified, and tampering is detectable.

Extend the trading platform for international markets - currency tables, historical exchange rates, and point-in-time rate lookups.

Design a risk monitoring system with exposure limits, margin thresholds, and portfolio snapshots for real-time alerting. It also links to the accounts table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a multi-stage workflow engine with parallel stages, multi-approver patterns, and stage dependencies for managing IPO deals.

Design a time-series data architecture - high-frequency tick ingestion, OHLCV candle aggregation, table partitioning, and tiered storage policies. It also links to the stocks table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a data warehouse star schema - fact tables for trades, dimension tables for date/client/stock, and Slowly Changing Dimensions (SCD Type 2).

The Act 3 capstone - join brokers and their roles, clients and accounts, orders, trades and ledger entries, commissions, risk profiles and an audit trail into ONE unified schema of 11+ tables.

Design distributed data schemas with vector clocks, conflict resolution, and sync tracking across 12 Lazarus missions where time dilation warps every timestamp.

Design a high-frequency IoT sensor schema for the Endurance spacecraft with raw ingestion, rollup aggregation tables, and threshold-based alert tracking.

Design a scientific data catalog with weighted scoring, measurement uncertainty tracking, and composite habitability rankings for candidate planets. It also links to the missions table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a bi-temporal data model that tracks crew vitals across two time dimensions: Valid Time (when the measurement was true in reality) and Transaction Time (when it was recorded in the database) - alongside each crew member's cryo-sleep sessions.

Design an inventory management system with consumption tracking, depletion projections, and scenario modeling for a spacecraft with finite, irreplaceable resources.

Design a relational message queue with guaranteed delivery, idempotent consumption, dead letter handling, and retry with exponential backoff for interstellar communications.

Model a directed weighted graph of spatial anomalies connected by wormholes using an adjacency list pattern, with computed shortest paths and self-referencing constraints.

Design a migration tracking and schema versioning system that records every structural change to the database, supports rollback, and maintains a live registry of all tables and columns.

Design a CQRS (Command Query Responsibility Segregation) architecture with separate write-optimized event logs and read-optimized projection tables for Tesseract gravity data.

Design the complete data architecture for Cooper Station - humanity's new home orbiting Saturn - with citizens, zones, resources, healthcare, governance, and cross-domain event tracking using DDD bounded contexts.

Design a billion-scale schema for 6.8 billion human pods with sub-millisecond lookups, partition strategies, and optimal key selection.

Design a self-referencing replication tree tracking Agent Smith clones, their lineage, generation depth, and the pods they override.

Design an anomaly detection system with configurable pattern rules, event correlation, and threat scoring for Matrix glitches.

Design a skill tree system with prerequisite DAGs, mutual exclusion constraints, and progression tracking for uploading combat programs into operatives.

Design a zero-trust access control system with MFA, device trust, session management, policy-based authorization, and real-time revocation after Cypher's betrayal.

Design a Git-like version control system in relational tables with repositories, branches, immutable commits, file snapshots, and diff tracking.

Design a multi-tenant architecture for 12 Matrix instances with tenant isolation, shared infrastructure tables, and provisioning workflows.

Design a configurable rules engine with nested condition trees, ordered action outcomes, rule versioning, and evaluation logging.

Design a phased migration orchestration system with checkpoint snapshots, rollback scripts, validation checks, and detailed logging for upgrading 6.8 billion rows.

Design the core of the Matrix data architecture across its bounded contexts - pods, agents, surveillance, identity, and cross-domain events - as the ultimate capstone challenge.

Design the product catalog for a fast-food chain where the same menu item can carry a different price - or not exist at all - at different locations.

Design the franchise agreement system: a franchisee licenses an exclusive territory, and every agreement keeps its dates so a territory's history of holders is preserved.

Model the kitchen order pipeline: an order moves through prep stations in sequence, and every station transition is timestamped.

Design the supplier and distribution schema that guarantees every restaurant gets identical ingredients, sourced through approved channels only.

Design the real estate holding structure that makes the company landlord to every one of its own franchisees - the twist that makes the empire actually profitable. It also links to the franchisees table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the schema that calculates monthly royalty owed on gross sales and reconciles it against supplier rebates earned on standardized purchasing. It also links to tables from earlier levels (franchise_agreements and suppliers) - add each as a stand-in with just an id so those links can be drawn.

Design a stock-tracking schema that covers per-location inventory levels, transfers between restaurants, and automatic reorder triggers. It also links to the restaurants table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the compliance and performance auditing system, including anonymous mystery-shopper inspections and a formal underperformance flagging process. It also links to tables from earlier levels (restaurants and franchise_agreements) - add each as a stand-in with just an id so those links can be drawn.

Design a cooperative marketing fund: every restaurant contributes a percentage of sales into a shared pool, and campaigns draw from it with regional allocation and ROI tracking. It also links to the restaurants table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Capstone: unify territory rights, real estate leasing, royalty calculation, and compliance into one integrated franchise-empire schema. It also links to tables from earlier levels (territories and properties) - add each as a stand-in with just an id so those links can be drawn.

Design the patient intake system: registration, admissions, symptom reporting, and triage priority for a fast-moving outbreak.

Model the exposure network: who was in contact with whom, when, and for how long - the graph that turns one case into a map of a hundred.

Design the specimen-to-result pipeline: collection, test ordering, and results, with turnaround time trackable at every stage. It also links to the patients table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the ward and bed inventory system, including per-bed occupancy and surge capacity for when a normal ward isn't enough. It also links to the patients table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the schema for issuing quarantine orders, tracking compliance check-ins, and logging violations that escalate to enforcement. It also links to the patients table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the vaccine batch and cold-chain shipment schema, with a continuous temperature log that proves - or disproves - a shipment stayed viable.

Design a reportable-disease case schema where every status change - suspected, probable, confirmed, ruled out - is logged, and public-health hand-offs are tracked explicitly. It also links to the patients table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the schema behind outbreak-cluster analysis: who infected whom, grouped into clusters, feeding a daily effective-reproduction-number time series. It also links to the patients table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the interoperability layer that lets health jurisdictions share case data under formal agreements, with consent tracked on every exchange. It also links to the reportable_cases table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Capstone: unify patient intake, contact tracing, quarantine, and case reporting into one command-center schema anchored on the patient.

Design the account and avatar system: one player can own several avatars, and every piece of an avatar's appearance is tracked as its own customization slot.

Design the item catalog and inventory system, distinguishing items an avatar is carrying from items an avatar currently has equipped. It also links to the avatars table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design a quest system where some quests require an earlier quest to be completed first - a prerequisite chain pointing back into the same table. It also links to the avatars table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the guild membership system as a many-to-many relationship between avatars and guilds, plus an invitation flow with two references back to the same avatars table. It also links to the avatars table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the ranked-match system: a per-mode skill rating, a match record, and a rich junction table capturing each participant's team and rating change. It also links to the avatars table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the item marketplace and a double-entry currency ledger where every trade produces a matching debit and credit. It also links to tables from earlier levels (avatars and items) - add each as a stand-in with just an id so those links can be drawn.

Design a crafting system where each recipe requires several material items in specific quantities, and an avatar's crafting jobs are queued, not instant. It also links to tables from earlier levels (items and avatars) - add each as a stand-in with just an id so those links can be drawn.

Design the schema behind a Halliday-style sequential puzzle hunt: ordered clues, per-avatar solve progress, a completion-time leaderboard, and an anti-cheat audit trail. It also links to the avatars table from an earlier level - add it as a stand-in with just an id so that link can be drawn.

Design the schema for transferring a player between regional servers, including a second table for resolving item and name conflicts the migration uncovers. It also links to tables from earlier levels (avatars and items) - add each as a stand-in with just an id so those links can be drawn.

Capstone: unify avatars, inventory, guilds, the marketplace, and quests into one integrated virtual-world schema anchored on the avatar.

Design the schema behind a URL shortener - unique short codes mapped to long URLs, optionally owned by a registered user.

Design the schema that backs a rate limiter's configuration and audit trail - not the hot-path counter itself (that lives in a cache), but what configures and logs it.

Design the schema for 1:1 and group messaging - conversations, membership, and an ordered message history.

Design the schema behind a social news feed - posts, who-follows-whom, and a materialized per-user feed table.

Design the schema for a photo-sharing app - posts with images, likes, and the follow graph.

Design the schema for hotel room reservations - hotels, their rooms, guests, and date-ranged bookings.

Design the schema for a ride-sharing platform - riders, drivers, ride requests, and the payment tied to each completed ride.

Design the schema for a payment processor - merchants, their customers, stored payment methods, charges, and refunds.

Design the schema for a food delivery platform - restaurants, their menus, customer orders with multiple line items, and driver assignment.

Design the schema for an event-ticketing platform - venues, seats, events, and the purchase that claims one specific seat for one specific event.