Phase Project: Build a Clean Inventory Schema — SQL Course

What you'll learn

Design, create, populate, validate, and safely modify a constrained inventory database from a written specification. 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 Schema design to a realistic data question
Apply Constraints to a realistic data question
Apply Seed data to a realistic data question
Apply Safe modifications 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
Specification	A written statement of entities and invariants	Resolve ambiguity before encoding schema
Seed fixture	Known data for demonstrations and tests	Include valid boundaries and deliberately rejected examples
Quality query	SQL detecting violated expectations	Keep checks runnable even when constraints also protect writes

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the clean inventory 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

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Verify project invariants

This audit query should return zero rows after every seed or migration.

verify_inventory.sql

SELECT 'negative_price' AS issue, id::text AS entity
FROM inventory.products WHERE price < 0
UNION ALL
SELECT 'orphan_stock', s.product_id::text
FROM inventory.stock AS s
LEFT JOIN inventory.products AS p ON p.id = s.product_id
WHERE p.id IS NULL;

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

A schema project is incomplete if it creates tables but does not prove invalid writes fail and safe changes affect the intended rows.

Independent workshop

Deliver a rebuildable inventory database with schema, seed, change, audit, and teardown scripts.

Your finished workshop must include:

Schema design
Constraints
Seed data
Safe modifications
Data-quality queries
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Specification: Resolve ambiguity before encoding schema
Seed fixture: Include valid boundaries and deliberately rejected examples
Quality query: Keep checks runnable even when constraints also protect writes

Quick check

1. Which rule best applies to Specification?

2. Which rule best applies to Seed fixture?

3. Which rule best applies to Quality query?

Next: Relational Modeling & Normalization