Schema Design Lab

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.