Test, Document, Defend, and Present the Design

Learning outcomes

A professional database design is not complete when DDL runs successfully. It must be tested, documented, observable, evolvable, and defensible. This final lesson turns the capstone into a design package another engineering team could review and operate.

Test categories

Constraint tests

Attempt invalid writes:

duplicate tenant + work_order_numberPartUsage quantity = 0closed_at before opened_atAsset references Customer from another tenantnegative inventory

Tests should prove the database rejects or safely handles them.

Temporal tests

Validate:

• adjacent ownership intervals allowed;
• overlapping ownership rejected;
• future ownership if supported;
• backdated correction path;
• current owner query.

Concurrency tests

Run concurrent sessions for:

consume last inventory unitassign two primary techniciansclose WorkOrder while another assignment startsedit same WorkOrder version twice

Idempotency tests

Replay the same request_id:

RecordPartUsage request 8c31...retry request 8c31...expected: one business effect

Tenant isolation tests

Create tenant A and tenant B with overlapping local IDs. Attempt cross-tenant:

• reads;
• updates;
• FK creation;
• search/projection access;
• support/admin operations.

Performance tests

Use representative data volumes and distributions. Capture:

EXPLAIN / execution planrows estimated vs actualrows scanned vs returnedp50 / p95 / p99throughputlock waitsI/Oindex size

Workload benchmark

Create a small benchmark suite:

Q1 WorkOrder lookupQ2 Asset historyQ3 Technician scheduleC1 Open WorkOrderC3 Record PartUsageR1 Monthly report

Use it before and after physical-design changes.

Migration tests

For every major schema change:

1. apply to empty database;
2. apply to production-like snapshot;
3. run mixed old/new application versions;
4. run backfill;
5. validate;
6. contract;
7. test recovery/roll-forward.

Core documentation set

• conceptual diagram;
• logical ER diagram;
• physical schema/DDL;
• data dictionary;
• business-rule catalogue;
• access-pattern catalogue;
• index rationale;
• transaction catalogue;
• security/tenant model;
• migration strategy.

Data dictionary example

Decision log

D-01 Use surrogate PKs + tenant-scoped alternate keys.D-02 Keep AssetOwnership temporal history.D-03 Keep DiagnosticCapture payload JSON with typed envelope.D-04 Use normalized OLTP source + derived list projection.D-05 Use expand-and-contract for destructive changes.

Record alternatives and reasons.

Traceability

Every important design choice should point backward to a requirement and forward to a test:

R-08 Tenant isolation -> tenant-aware keys/RLS -> TEST-SEC-004 cross-tenant FK rejected

Design defense structure

Present in this order:

1. domain and scope;
2. core entities and grain;
3. key relationships/time semantics;
4. normalization rationale;
5. transaction/invariant design;
6. indexes/workload evidence;
7. security/governance;
8. evolution strategy;
9. known tradeoffs and future work.

Defend tradeoffs, not perfection

A strong design says:

We chose adjacency list because hierarchy moves are frequentand subtree reads are not a hot path.We chose JSON for diagnostic vendor payloads because shape varies,but promoted severity and timestamps for operational querying.

Tradeoffs make design credible.

Known limitations

Document:

• analytics may require warehouse offload at higher scale;
• global Part catalog may change tenant-local SKU assumptions;
• advanced temporal overlap constraint syntax is DBMS-specific;
• graph-like routing is outside the relational core;
• billing/payment domain is intentionally incomplete.

Production-readiness checklist

• Every table has defined grain.
• Every important business key has a constraint.
• Tenant ownership is enforceable.
• Transactions preserve invariants.
• Retries are idempotent.
• Indexes map to named access patterns.
• Sensitive data is minimized and scoped.
• Audit/provenance exists for critical actions.
• Derived data has freshness/rebuild rules.
• Schema changes have compatible migration paths.
• Retention/deletion and lineage are documented.
• Tests cover invalid and concurrent behavior.

Final capstone exercise

Course synthesis

What you should now be able to do

After all 90 lessons, you should be able to:

• turn narrative requirements into data models;
• reason formally about keys and dependencies;
• normalize without losing business meaning;
• design transaction and concurrency boundaries;
• choose indexes from workloads;
• denormalize with explicit ownership;
• model time, hierarchies, JSON, and flexible attributes;
• evolve schemas safely;
• design for tenant security and governance;
• defend a production database design with evidence.

Course completion

You have completed Course 02 — Data Modeling and Database Design. The strongest next step is to implement the capstone in a real relational DBMS, generate representative data, run the concurrency and migration tests, inspect query plans, and iterate from measurement.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Martin Kleppmann, Designing Data-Intensive Applications.
• Pramod Sadalage and Scott Ambler, Refactoring Databases.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit.
• Bill Karwin, SQL Antipatterns.
• DBMS vendor documentation for transactions, indexing, security, and online schema changes.