Phase 2 Capstone: Designing a 25+ Table Enterprise Schema
You'll learn to
- -Combine every technique from Phase 2 - normalization, RBAC, event sourcing, sharding, multi-tenancy - into one coherent enterprise schema
- -Practice explaining a large schema's design decisions the way a staff-level review actually demands
This closing chapter mirrors Phase 1's mock-interview capstone, applied to schema design: rather than one more isolated technique, it walks through how an unfamiliar, large-scale scenario draws on nearly everything covered across Phase 2, the same way a real staff-level schema review actually unfolds.
The Scenario: A Rules-Engine Migration Platform
Picture a platform (mirroring the Lab's own capstone, The Architect's Masterpiece) that lets multiple independent client organizations define business rules, run them against incoming data streams, version and roll back their rule sets, and audit every decision the rules engine made - a scenario that, deliberately, does not map onto any single chapter's technique alone.
Layer 1: Multi-Tenancy Foundation
CREATE TABLE tenants (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE users (
id INTEGER PRIMARY KEY,
tenant_id INTEGER NOT NULL REFERENCES tenants(id),
email TEXT NOT NULL
);
-- Row-Level Security policies (Multi-Tenancy chapter) apply across every
-- tenant-scoped table from here on, not repeated per-table in this outline.Layer 2: RBAC for Platform Permissions
Reusing the RBAC chapter's three-table shape directly: roles, permissions, and the two junction tables connecting them to users - a platform administrator role can manage rules; a viewer role can only see audit results, all driven by data, not hardcoded checks.
Layer 3: Rules as Versioned, Data-Driven Content
CREATE TABLE rule_sets (id INTEGER PRIMARY KEY, tenant_id INTEGER NOT NULL, name TEXT NOT NULL);
CREATE TABLE rule_set_versions ( -- version history, same shape as content_versions
id INTEGER PRIMARY KEY,
rule_set_id INTEGER NOT NULL REFERENCES rule_sets(id),
parent_version_id INTEGER REFERENCES rule_set_versions(id),
rules_definition TEXT NOT NULL, -- the actual rules, as structured data
author_id INTEGER NOT NULL REFERENCES users(id),
is_active BOOLEAN NOT NULL DEFAULT FALSE,
committed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- "rollback" is simply activating a previous version - no data is ever
-- destroyed, exactly the append-only philosophy from Event SourcingLayer 4: Event Sourcing for the Audit Trail
Every decision the rules engine makes needs to be reconstructable later - "why was this transaction flagged on this date" - which is the append-only, event-sourced pattern applied once more: each evaluation is an immutable row, never updated, referencing exactly which rule_set_version made the decision.
CREATE TABLE rule_evaluations (
id BIGINT PRIMARY KEY,
tenant_id INTEGER NOT NULL,
rule_set_version_id INTEGER NOT NULL REFERENCES rule_set_versions(id),
input_data TEXT NOT NULL,
decision TEXT NOT NULL,
evaluated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Layer 5: Sharding for Evaluation Volume
rule_evaluations is the table that will dwarf every other table in this schema by volume - a natural candidate for the sharding chapter's technique, keyed on tenant_id (each tenant's evaluations tend to be queried together, and this keeps one very large tenant from needing every query fanned out across all other tenants' shards too).
Stepping Back: Naming the Full Set of Techniques Applied
- -Relational Modeling Foundations: every table still has correct PKs, FKs, and normalized structure underneath everything else.
- -Intermediate Modeling: RBAC's many-to-many junctions, reused directly for platform permissions.
- -Advanced Transactional Modeling: event-sourced, append-only rule_evaluations - the exact pattern from the ledger and audit-trail chapters.
- -Distributed & Large-Scale Modeling: versioned rule sets (schema versioning chapter's philosophy, applied to business content instead of the schema itself).
- -Planet-Scale Schema Design: sharding rule_evaluations by tenant, and Row-Level Security enforcing tenant isolation throughout.
Beyond Level 50: Three More Acts of Advanced Practice
Levels 51 through 80 are a second, later addition to the Lab, covering domains this course doesn't walk through directly: The Founder (51-60) applies these same techniques to e-commerce and retail-franchise schemas - standardized catalogs, territory licensing, supply chains. Contagion (61-70) applies them to healthcare and epidemiology - patient records, contact-tracing graphs, cold-chain logistics. Ready Player One (71-80) applies them to gaming and virtual economies - inventory systems, guild membership, double-entry currency ledgers. None of it is a new technique beyond what this course already covers - it's the same normalization, junction-table, and event-sourcing judgment calls, applied to unfamiliar domains, closing with a second capstone at Level 80.
On a review this large, the strongest closing move is exactly what Phase 1's capstone modeled: naming which techniques were applied and why, rather than assuming the diagram speaks for itself - a staff-level schema review is graded on the reasoning as much as the tables themselves.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Level 50: The Architect's Masterpiece is the capstone of the original 5 acts. Level 80: The OASIS goes even further, a second capstone that closes out the full 80-level Lab after three more acts of advanced practice.
Both courses close the same way: not with a new technique, but with a synthesis case that draws on everything before it. SQL Lab's own capstone, "Name the Mastermind," chains CTEs through the entire toolbox to crack the case - the query-side twin of this chapter's schema-side capstone.