Chapter 18 · Capstone: Design a Production-Ready Database
Normalize and Implement the Relational Schema
Normalize the capstone model, map it into a relational schema, choose keys and constraints, write production-oriented DDL, and validate the schema against representative data.
Learning outcomes
Now the capstone becomes executable. You will normalize the model, choose keys and constraints, map temporal and associative entities, and create production-oriented DDL. The goal is a schema whose structure expresses the domain rather than relying on application code to remember every rule.
Validate the model against functional dependencies and normal forms.
Write relational tables with explicit keys and constraints.
Separate current, historical, and derived facts correctly.
Load representative test data and validate invariants.
Normalization target
Use 3NF/BCNF as a practical baseline. Higher normal forms matter when real multivalued or join dependencies exist. Do not decompose merely to maximize table count.
Functional-dependency review
(tenant_id, work_order_number) -> work_order_id, asset_id, status_code, opened_at(tenant_id, sku) -> part_id, description, category_idassignment_id -> work_order_id, technician_id, role_code, started_at, ended_atAvoid transitive descriptive duplication
Do not store:
WorkOrder.customer_nameas source-of-truth state merely because WorkOrder → Asset → current Customer exists. If needed for performance, create a governed projection later.
Tenant table
CREATE TABLE tenant ( tenant_id BIGINT PRIMARY KEY, tenant_slug TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL, created_at TIMESTAMP NOT NULL);Customer
CREATE TABLE customer ( customer_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, customer_number TEXT NOT NULL, display_name TEXT NOT NULL, created_at TIMESTAMP NOT NULL, UNIQUE (tenant_id, customer_number), UNIQUE (tenant_id, customer_id), FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id));The extra composite unique key supports tenant-aware foreign keys where desired.
Asset
CREATE TABLE asset ( asset_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, manufacturer_code TEXT NOT NULL, serial_number TEXT NOT NULL, created_at TIMESTAMP NOT NULL, UNIQUE (tenant_id, manufacturer_code, serial_number), UNIQUE (tenant_id, asset_id), FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id));Asset ownership history
CREATE TABLE asset_ownership ( asset_ownership_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, asset_id BIGINT NOT NULL, customer_id BIGINT NOT NULL, valid_from TIMESTAMP NOT NULL, valid_to TIMESTAMP NULL, CHECK (valid_to IS NULL OR valid_to > valid_from), FOREIGN KEY (tenant_id, asset_id) REFERENCES asset(tenant_id, asset_id), FOREIGN KEY (tenant_id, customer_id) REFERENCES customer(tenant_id, customer_id));Non-overlap requires an additional DBMS-specific constraint or transactional validation.
WorkOrder
CREATE TABLE work_order ( work_order_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, work_order_number TEXT NOT NULL, asset_id BIGINT NOT NULL, status_code TEXT NOT NULL, problem_description TEXT NULL, opened_at TIMESTAMP NOT NULL, closed_at TIMESTAMP NULL, version BIGINT NOT NULL DEFAULT 1, UNIQUE (tenant_id, work_order_number), UNIQUE (tenant_id, work_order_id), FOREIGN KEY (tenant_id, asset_id) REFERENCES asset(tenant_id, asset_id), CHECK (closed_at IS NULL OR closed_at >= opened_at));Technician
CREATE TABLE technician ( technician_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, employee_number TEXT NOT NULL, display_name TEXT NOT NULL, active_flag BOOLEAN NOT NULL, UNIQUE (tenant_id, employee_number), UNIQUE (tenant_id, technician_id));Assignment
CREATE TABLE work_order_assignment ( assignment_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, work_order_id BIGINT NOT NULL, technician_id BIGINT NOT NULL, role_code TEXT NOT NULL, started_at TIMESTAMP NOT NULL, ended_at TIMESTAMP NULL, CHECK (ended_at IS NULL OR ended_at > started_at), FOREIGN KEY (tenant_id, work_order_id) REFERENCES work_order(tenant_id, work_order_id), FOREIGN KEY (tenant_id, technician_id) REFERENCES technician(tenant_id, technician_id));Part
CREATE TABLE part ( part_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, sku TEXT NOT NULL, description TEXT NOT NULL, UNIQUE (tenant_id, sku), UNIQUE (tenant_id, part_id));PartInventory
CREATE TABLE part_inventory ( tenant_id BIGINT NOT NULL, part_id BIGINT NOT NULL, available_qty INTEGER NOT NULL, PRIMARY KEY (tenant_id, part_id), CHECK (available_qty >= 0), FOREIGN KEY (tenant_id, part_id) REFERENCES part(tenant_id, part_id));PartUsage
CREATE TABLE part_usage ( part_usage_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, work_order_id BIGINT NOT NULL, part_id BIGINT NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), charged_unit_price NUMERIC(12,2) NOT NULL CHECK (charged_unit_price >= 0), recorded_at TIMESTAMP NOT NULL, FOREIGN KEY (tenant_id, work_order_id) REFERENCES work_order(tenant_id, work_order_id), FOREIGN KEY (tenant_id, part_id) REFERENCES part(tenant_id, part_id));DiagnosticCapture
CREATE TABLE diagnostic_capture ( capture_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, work_order_id BIGINT NOT NULL, source_system TEXT NOT NULL, schema_version INTEGER NOT NULL, severity_code TEXT NULL, captured_at TIMESTAMP NOT NULL, payload_json JSON NOT NULL, FOREIGN KEY (tenant_id, work_order_id) REFERENCES work_order(tenant_id, work_order_id));Status history
CREATE TABLE work_order_status_event ( status_event_id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, work_order_id BIGINT NOT NULL, from_status TEXT NULL, to_status TEXT NOT NULL, occurred_at TIMESTAMP NOT NULL, actor_id BIGINT NULL, request_id TEXT NULL);Audit event
CREATE TABLE audit_event ( audit_event_id BIGINT PRIMARY KEY, tenant_id BIGINT NULL, occurred_at TIMESTAMP NOT NULL, actor_type TEXT NOT NULL, actor_id TEXT NULL, action_code TEXT NOT NULL, entity_type TEXT NOT NULL, entity_id TEXT NOT NULL, request_id TEXT NULL, metadata_json JSON NULL);Constraint gaps to resolve
Some invariants require more than basic DDL:
- no overlapping AssetOwnership intervals;
- one active primary assignment;
- valid WorkOrder state transitions;
- inventory decrement under concurrency;
- closed WorkOrder has no active assignments.
These move into transactions, partial/exclusion constraints, or triggers depending on DBMS.
Representative seed data
Create test cases including:
- two tenants with overlapping business identifiers;
- one asset ownership transfer;
- one WorkOrder with two technicians;
- part usage with inventory decrement;
- diagnostic JSON from two schema versions;
- closed and cancelled work orders.
Normalization review
Verify:
- no repeating groups;
- no partial dependencies on composite business keys;
- no transitive descriptive facts stored in the wrong relation;
- associative entities carry relationship facts;
- derived summaries are not confused with authoritative state.
Checkpoint
Historical price
Part has current_price, while PartUsage records charged_unit_price. Is that duplication wrong?
Review answer
No. The two values have different determinants and time semantics. Part.current_price is current catalog state; PartUsage.charged_unit_price is the immutable price charged for a specific transaction.
Summary and next lesson
The capstone now has a normalized relational core with explicit tenant-aware keys, temporal relationships, associations, JSON boundaries, and integrity constraints. The next lesson hardens it for production workloads, concurrency, security, indexing, and evolution.
References
- Course Chapters 5–9 for mapping, constraints, dependencies, and normalization.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
- DBMS documentation for exact DDL syntax and advanced constraints.