What you'll learn
Design, build, secure, tune, operate, and present a complete commerce database with transactional and analytical workloads. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.
By the end of this lesson, you'll be able to:
- Apply Requirements and ERD to a realistic data question
- Apply Constrained OLTP schema to a realistic data question
- Apply Advanced analytics to a realistic data question
- Apply Performance evidence to a realistic data question
Core mental model
SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.
| Concept | What it means | Decision rule |
|---|---|---|
| Architecture decision | A documented choice with alternatives and consequences | Connect each database feature to a requirement or measured workload |
| Acceptance evidence | Executable proof that requirements hold | Include correctness, concurrency, security, performance, and recovery |
| Portfolio narrative | A clear explanation of problem, constraints, decisions, results, and learning | Show depth and tradeoffs rather than a technology list |
Professional workflow
Work from a defined question and result grain, then verify correctness before performance.
- State the production commerce database question and the exact grain of the expected result.
- Inspect table definitions, keys, constraints, representative values, and row counts.
- Write the smallest correct query with explicit columns, aliases, and predicates.
- Test missing, duplicate, boundary, and NULL cases before trusting the result.
- Inspect the execution plan or affected rows when cost or data change matters.
- Save the query with its assumptions, parameters, verification, and recovery notes.
Make results explainable
Guided SQL lab
Create a capstone acceptance catalog
The catalog makes each requirement traceable to a repeatable SQL test or operational artifact.
CREATE TABLE engineering.acceptance_evidence (
requirement_key text PRIMARY KEY,
description text NOT NULL,
evidence_type text NOT NULL CHECK (evidence_type IN ('query','test','plan','runbook','restore')),
artifact_path text NOT NULL,
expected_result text NOT NULL,
verified_at timestamptz
);Production practice
Contract
Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.
Verification
Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.
Operations
Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.
Common failure mode
Independent workshop
Design, build, secure, tune, recover, and present a commerce database serving both transactional and analytical workloads.
Your finished workshop must include:
- Requirements and ERD
- Constrained OLTP schema
- Advanced analytics
- Performance evidence
- Security and recovery
- Verification notes and edge-case evidence
Definition of done
Recap & quick check
Key takeaways
- Architecture decision: Connect each database feature to a requirement or measured workload
- Acceptance evidence: Include correctness, concurrency, security, performance, and recovery
- Portfolio narrative: Show depth and tradeoffs rather than a technology list
Quick check
1. Which rule best applies to Architecture decision?
2. Which rule best applies to Acceptance evidence?
3. Which rule best applies to Portfolio narrative?
Next: Continue practicing with the capstone and your own production-shaped datasets.