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.

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.

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.

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.

Design the events system with complex M:M RSVP metadata, recurring event patterns, and invitation tracking.

Design granular privacy controls - visibility enums, custom audience lists, and a blocked users system for row-level security modeling.

Build the complete notification system - type registry, user preferences, read/unread tracking, and aggregation grouping for the Act 2 capstone.

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.

Design a trade execution system with double-entry bookkeeping - every buy creates a debit and credit, and the ledger must ALWAYS balance.

Design tiered commission calculations stored as configurable data - rates that change over time, splits between broker and firm, and payout tracking.

Model a state machine in the database - defined states, valid transitions between them, and a document management workflow for KYC compliance.

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.

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.

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 - integrate brokers, clients, portfolios, trading, commissions, compliance, risk, and reporting into ONE unified schema of 15+ 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.

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).

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, weighted 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 COMPLETE Matrix data architecture spanning 8 domains with bounded contexts, cross-domain event integration, and 25+ table coordination 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 no two agreements may overlap.

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.

Design the schema that calculates monthly royalty owed on gross sales and reconciles it against supplier rebates earned on standardized purchasing.

Design a stock-tracking schema that covers per-location inventory levels, transfers between restaurants, and automatic reorder triggers.

Design the compliance and performance auditing system, including anonymous mystery-shopper inspections and a formal underperformance flagging process.

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.

Capstone: unify territory rights, real estate leasing, royalty calculation, and supply chain into one integrated franchise-empire schema.

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.

Design the ward and bed inventory system, including per-bed occupancy and surge capacity for when a normal ward isn't enough.

Design the schema for issuing quarantine orders, tracking compliance check-ins, and logging violations that escalate to enforcement.

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.

Design the schema behind outbreak-cluster analysis: who infected whom, grouped into clusters, feeding a daily effective-reproduction-number time series.

Design the interoperability layer that lets health jurisdictions share case data under formal agreements, with consent tracked on every exchange.

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 control an avatar, and every piece of that 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.

Design a quest system where some quests require an earlier quest to be completed first - a prerequisite chain pointing back into the same table.

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.

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.

Design the item marketplace and a double-entry currency ledger where every trade produces a matching debit and credit.

Design a crafting system where each recipe requires several material items in specific quantities, and an avatar's crafting jobs are queued, not instant.

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.

Design the schema for transferring a player between regional servers, including a second table for resolving item and name conflicts the migration uncovers.

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