Chapter 18 · Capstone: Design a Production-Ready Database
Capstone Requirements and Domain Discovery
Begin the capstone by discovering the domain, interviewing stakeholders, defining scope, extracting business rules, documenting assumptions, and turning narrative requirements into a modeling backlog.
Capstone mission
This chapter combines the entire course into one production-oriented design exercise. You will design a database for a fictional multi-tenant repair and field-service platform called WorkshopHub. Unlike the earlier examples, the capstone treats requirements as incomplete and sometimes conflicting—closer to a real project.
Discover the domain before drawing tables.
Separate facts, rules, assumptions, and open questions.
Define scope and non-goals explicitly.
Produce a requirements-to-model traceability sheet.
Scenario
WorkshopHub serves independent repair businesses. Each tenant manages customers, assets, technicians, parts, work orders, assignments, diagnostic captures, part usage, invoices, and operational history. The platform must support both daily OLTP work and downstream reporting.
Stakeholder interviews
Imagine five stakeholders:
- service manager;
- dispatcher;
- technician;
- inventory manager;
- billing/reporting analyst.
Each uses the same data differently.
Ask fact-seeking questions
Bad:
Do you want a Customer table?Better:
Can one customer own several assets?Can ownership change?Do you need to know who owned an asset last year?Can two tenants share the same customer?What identifies a customer inside one tenant?Initial narrative requirements
- Each tenant manages its own customers and technicians.
- Customers can own multiple assets.
- An asset can change owner over time.
- Each work order belongs to one asset.
- Several technicians can work on one work order.
- One assignment can be primary.
- Parts may be consumed by work orders.
- Inventory must not go negative.
- Work orders follow controlled state transitions.
- Diagnostic payloads vary by manufacturer.
- Historical and audit reporting are required.
Facts versus rules
| Statement | Type |
|---|---|
| A WorkOrder references one Asset | Structural fact/rule |
| Inventory cannot be negative | Invariant |
| Managers prefer newest orders first | Access pattern |
| Diagnostics vary by vendor | Variability requirement |
| Audit records retained longer | Governance rule |
Assumptions register
A-01 Customer identity is tenant-scoped.A-02 WorkOrder numbers are unique per tenant.A-03 One asset has at most one active owner at a time.A-04 One WorkOrder has at most one active primary assignment.A-05 Part catalog may be tenant-local in v1.Assumptions are not truths. They must be reviewed.
Open questions register
Q-01 Can a technician work for multiple tenants?Q-02 Can a WorkOrder exist without an assigned technician?Q-03 Are invoices part of this project scope?Q-04 Do customers need multiple service addresses?Q-05 Must diagnostic payloads be searchable across tenants?Scope boundaries
For the capstone, include:
- tenant ownership;
- customer/asset management;
- work orders and assignments;
- parts and usage;
- diagnostic captures;
- status/audit history;
- indexes and migration plan.
Non-goals
Exclude:
- full accounting ledger;
- payment-card storage;
- route optimization;
- graph-based social features;
- warehouse implementation details.
Non-goals prevent uncontrolled model expansion.
Candidate entities
TenantCustomerAssetAssetOwnershipTechnicianWorkOrderWorkOrderAssignmentWorkOrderStatusWorkOrderStatusEventPartPartInventoryPartUsageDiagnosticCaptureAuditEventCandidate business identifiers
| Entity | Business identifier candidate |
|---|---|
| Tenant | tenant_slug / external account code |
| Customer | customer_number per tenant |
| Asset | manufacturer + serial number |
| WorkOrder | work_order_number per tenant |
| Part | SKU per tenant |
| Technician | employee_number per tenant |
Access-pattern backlog
Q1 WorkOrder by tenant + work_order_numberQ2 Asset history newest firstQ3 Active assignments by technicianQ4 Part by tenant + SKUQ5 Active work queue by tenant/statusC1 Open WorkOrderC2 Assign TechnicianC3 Record Part UsageC4 Close WorkOrderR1 Monthly repairs by statusR2 Part consumption by monthQuality attributes
Record non-functional requirements:
tenant isolation: strictOLTP p95 target: < 150 ms for hot pathsauditability: requireddiagnostic JSON freshness: immediatereporting freshness: <= 15 minschema changes: rolling deployment, no planned downtimeTraceability matrix
| Requirement | Model element | Validation |
|---|---|---|
| Asset owner history | AssetOwnership | No overlapping active intervals |
| Many technicians/order | WorkOrderAssignment | M:N association |
| No negative inventory | PartInventory | Atomic conditional update |
| Tenant isolation | tenant_id ownership | Tenant-aware keys/RLS tests |
Deliverable for Lesson 1
Create a short design brief containing:
- scope and non-goals;
- stakeholders;
- candidate entities;
- business rules;
- access patterns;
- assumptions/open questions;
- quality/security requirements.
Checkpoint
Requirement ambiguity
A stakeholder says, “An Asset belongs to a Customer.” What is still unknown?
Review answer
Whether ownership can change, whether history is required, whether joint ownership is possible, whether the relationship is tenant-scoped, whether an Asset may temporarily have no owner, and what identifies the owner at a point in time.
Summary and next lesson
The capstone begins with disciplined discovery. Tables come after facts, rules, access patterns, scope, and assumptions. The next lesson reviews the conceptual and logical model before any physical implementation decisions are locked in.
References
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
- Martin Fowler, analysis patterns and domain modeling writings.
- Course Chapters 1–4 for requirements, entities, relationships, and notation.