Chapter 18 · Capstone: Design and Query a Complete Database
Capstone Requirements and Conceptual Model
A successful database begins before CREATE TABLE. In this capstone you will convert an intentionally imperfect business brief into explicit scope, durable business rules, entity identities, relationship cardinalities, history requirements, and measurable acceptance criteria.
Learning outcomes
Learning outcomes
Convert an ambiguous business brief into an explicit problem statement, scope, actors, use cases, and non-functional requirements.
Define the grain, identity, lifecycle, and history policy for every major entity.
Derive cardinalities and optionality from business rules instead of drawing relationships by intuition.
Separate conceptual requirements from logical tables and product-specific implementation details.
Write measurable acceptance criteria that later schema, query, transaction, security, and recovery tests can prove.
Capstone scenario: Northstar Supply
Northstar Supply is a small business-to-business commerce company that sells technical books, office equipment, and computing accessories. Customers place orders through an application, choose a saved shipping address, and receive products from one of several warehouses. Operations staff need accurate stock, payment, shipment, and audit information. Management needs monthly revenue, product performance, customer value, and inventory-risk reports.
Customer service
Find a customer, inspect an order, explain its status, and confirm the shipping destination without exposing unrelated customer data.
Fulfillment
See submitted or paid orders, reserve available inventory, create shipments, and avoid allocating the same stock twice.
Finance
Reconcile captured and refunded payments with order totals and investigate exceptions.
Analytics
Measure recognized revenue, product demand, regional performance, repeat purchasing, and stock risk from reproducible queries.
Do not jump directly from prose to tables. Each transition should leave reviewable evidence.
Scope and boundary decisions
| Area | In scope for version 1 | Explicitly out of scope |
|---|---|---|
| Customers | Account identity, status, region, reusable shipping addresses | Authentication credentials, marketing consent center, customer-support tickets |
| Catalog | Category hierarchy, SKU, product name, current price, active state | Variants, bundles, supplier procurement, multi-currency price lists |
| Inventory | On-hand, reserved, reorder point per warehouse and product | Lot tracking, serial numbers, damaged stock, inter-warehouse transfers |
| Orders | Order header, immutable line-price snapshots, status, idempotency key | Discount engine, tax calculation, returns authorization |
| Payments | Provider reference, amount, lifecycle state, paid timestamp | Card data, settlement batches, chargeback workflow |
| Shipments | One warehouse per shipment, tracking, lifecycle timestamps | Carrier rate shopping, multi-package detail, shipment-line allocation |
| Operations | Audit events, backup and restore procedure, query-plan evidence | High availability, cross-region replication, full observability platform |
Actors and prioritized use cases
ACTOR customer-service UC-01 Search customer by email. UC-02 Show order header, lines, totals, payment, and shipment.ACTOR fulfillment-worker UC-03 List orders ready for fulfillment in deterministic priority order. UC-04 Reserve stock atomically and create a shipment.ACTOR finance-analyst UC-05 Reconcile captured/refunded payments against order totals.ACTOR business-analyst UC-06 Report monthly recognized revenue by region. UC-07 Rank products and customers by net revenue.ACTOR platform-engineer UC-08 Restore a verified backup within the recovery objective. UC-09 Prove least-privilege access and query-plan expectations.Conceptual entities and grain
| Entity | One row represents | Stable identity | Important lifecycle |
|---|---|---|---|
| Customer | One commercial customer account | customer_id; email is an alternate key | active → suspended/closed |
| Address | One named address saved by one customer | address_id; customer + label unique | editable until used; later changes create a new address row so order history remains stable |
| Category | One catalog classification node | category_id; category_code alternate key | optional parent creates a hierarchy |
| Product | One sellable SKU | product_id; sku alternate key | active/inactive without deleting history |
| Inventory | One product balance at one warehouse | warehouse_id + product_id | on_hand and reserved change transactionally |
| Sales order | One checkout request | order_id; order_number and request_key alternate keys | draft → submitted/paid/packed/shipped or cancelled |
| Order item | One product line within one order | order_id + line_no | price is a snapshot, not a lookup of today’s price |
| Payment | One provider-side payment attempt/result | payment_id; provider_ref alternate key | authorized/captured/failed/refunded |
| Shipment | One fulfillment movement from one warehouse | shipment_id; tracking code optional alternate key | pending/packed/shipped/delivered/returned |
| Audit event | One append-only record of a meaningful action | event_id | never updated as business state |
Relationships, cardinality, and optionality
The diagram is a reading path, not a substitute for precise relationship rules.
| Relationship | Cardinality | Reasoning test |
|---|---|---|
| Customer → Address | one-to-many; address requires a customer | Can a customer have two saved addresses? Yes. Can an address exist without an owner? No. |
| Customer → Sales order | one-to-many; order requires a customer | Historical orders survive customer suspension; the account is not physically deleted. |
| Sales order → Order item | one-to-many; submitted orders require at least one line | The database enforces line ownership; service logic verifies the at-least-one rule at submission. |
| Product ↔ Warehouse | many-to-many resolved by Inventory | Balances have relationship attributes: on_hand, reserved, reorder_point, updated_at. |
| Sales order → Payment | one-to-many in the model | Retries, failures, and refunds may create multiple payment records over time. |
| Sales order → Shipment | one-to-many in the model | Version 1 seed data uses one shipment, but the model does not prevent future split shipments. |
| Category → Category | optional recursive parent | A root category has no parent; a child has one parent. |
Business rules as testable invariants
BR-01 customer.email is case-insensitively unique.BR-02 each customer has at most one default address; an order address belongs to that customer.BR-03 inventory.reserved is never negative and never exceeds on_hand.BR-04 order_item.quantity is positive; price is a nonnegative snapshot.BR-05 order_item.line_total = quantity × unit_price.BR-06 a request_key creates at most one sales order.BR-07 cancelled orders do not count as recognized revenue.BR-08 delivered_at may exist only when shipped_at exists.BR-09 inactive products remain queryable for historical orders.BR-10 payment provider references are globally unique.BR-11 destructive operations require explicit retention or archival policy.
Classify each rule by enforcement location: key, foreign key,
CHECK, generated column, transaction, trigger,
application service, scheduled reconciliation, or operating
procedure. A rule is incomplete until its enforcement and test
are named.
History, derived values, and ownership
| Question | Decision | Why |
|---|---|---|
| Which price belongs on an old order? | Store unit_price_cents on order_item | Current catalog price may change; the order line is historical evidence. |
| Should order total be stored? | Derive from lines in reports; verify payment against the derived total | Avoid two independently mutable sources of truth in this course scope. |
| Can customers be deleted? | Prefer status=closed; cascade addresses only in controlled test data | Orders and financial records need durable ownership history. |
| Who owns stock reservation? | One transactional order-placement service | A single invariant owner reduces race conditions and inconsistent retries. |
| Where is recognized revenue defined? | Captured payments for non-cancelled orders | The metric definition becomes query and documentation, not tribal knowledge. |
Non-functional requirements
| Quality | Acceptance target |
|---|---|
| Correctness | Foreign keys enabled; integrity checks pass; transaction tests prove no partial order or over-reservation. |
| Performance | Named operational queries use intended indexes on representative data; plans are recorded. |
| Security | Application code binds values; read-only and write roles are separated in the production design. |
| Recoverability | A backup is restored into a separate database and verified with integrity and row-count checks. |
| Auditability | Meaningful order and inventory changes carry actor, timestamp, entity, action, and details. |
| Maintainability | Schema, seed, queries, tests, migration notes, data dictionary, and runbook are version controlled. |
Acceptance criteria and repository contract
northstar-capstone/ README.md db/ 001_schema.sql 002_seed.sql 003_indexes.sql 004_views.sql queries/ operational.sql analytical.sql verification.sql tests/ test_database.py runbooks/ backup_restore.md release_checklist.md docs/ conceptual-model.md data-dictionary.md decisions/ 001-price-snapshot.md 002-inventory-reservation.md presentation/ capstone-outline.mdRequirements review
- Why is order-item price a snapshot rather than a foreign lookup of current product price?
- Why does the Inventory relationship need its own entity?
- Which “at least one order line” rule is difficult to express with a simple row-level constraint?
- What makes an acceptance criterion stronger than a design intention?
Review the answers
Historical prices must remain stable.
Inventory has relationship attributes and a composite identity.
Row constraints cannot normally inspect the complete child set at order-submission time, so a transaction or deferred workflow must validate it.
Acceptance criteria name observable evidence, expected outcomes, and failure behavior.
Lesson summary
- Requirements define scope, actors, decisions, quality targets, and exclusions before tables.
- Every entity needs an explicit grain, stable identity, lifecycle, and history policy.
- Cardinality follows business questions, not diagram aesthetics.
- Business rules should be assigned to an enforcement mechanism and a test.
- The capstone repository is an engineering product, not only a SQL script.