Modeling the Database Schema

The sketch from Scoping the Capstone API fits on ten lines. Now it becomes SQL, and this is where every hand-wave from the sketch gets a price: a missing unique index, a FLOAT price, a column that cannot hold the value the API will send. The first migration ships three tables — products, stock_levels, users — plus the join table the relationships demand.

-- 0001_create_core.sql
CREATE TABLE users (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email         VARCHAR(255) NOT NULL,
    password_hash CHAR(60)     NOT NULL,          -- bcrypt output is exactly 60 chars
    role          ENUM('admin','worker') NOT NULL DEFAULT 'worker',
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sku         VARCHAR(64)  NOT NULL,
    name        VARCHAR(255) NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    archived_at DATETIME     NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_products_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_levels (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id  BIGINT UNSIGNED NOT NULL,
    location_id BIGINT UNSIGNED NOT NULL,
    quantity    INT UNSIGNED NOT NULL DEFAULT 0,   -- on hand can never be negative
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                            ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_stock_product_location (product_id, location_id),
    CONSTRAINT fk_stock_product  FOREIGN KEY (product_id)
        REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_stock_location FOREIGN KEY (location_id)
        REFERENCES locations(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Read the file as a set of deliberate answers to the questions your queries will ask:

• UNIQUE KEY uq_products_sku is what lets GET /products/{sku} be a single indexed lookup and makes POST returning 422 sku_taken a database fact rather than a check-then-insert race. The same applies to uq_stock_product_location: without it, two concurrent adjustments create two rows for one product-location pair, and every SUM(quantity) permanently double-counts. A unique constraint is an application invariant the database enforces for you.
• Types encode policy. unit_price DECIMAL(10,2) because binary floats cannot represent 0.1 exactly and totals will disagree by cents. quantity INT UNSIGNED so a bug that would write −5 fails loudly at the insert. password_hash CHAR(60) because bcrypt output has a fixed length — a VARCHAR(30) silently truncates credentials and locks the user out forever.
• DEFAULT CHARSET=utf8mb4 is not boilerplate: the older utf8 alias in MySQL is three-byte UTF-8 and rejects emoji and rare CJK. A customer named Zoë already needs it, and a 4-byte-rejecting column is exactly the kind of failure you met in the string-functions material.
• updated_at ... ON UPDATE CURRENT_TIMESTAMP gives you meta.updated_at for free on every write, which is what makes keyset pagination and cache validation possible later.
• Indexes follow the query list, not superstition: the unique keys cover WHERE sku = ? and WHERE product_id = ? AND location_id = ?; pagination sorts by id, already the primary key. Adding an index on every column only slows writes.

Naming conventions matter more than they look: snake_case, singular table names for rows (products plural is the common REST-side convention — pick one and never mix), and constraint names you can recognize in a SHOW ENGINE INNODB STATUS dump six months later.

To apply it: mysql -u app -p inventory < 0001_create_core.sql works once, but a capstone ships a runner — numbered files plus a schema_migrations table so php bin/migrate.php up applies only what is pending, and status shows what has run where. You will build exactly that in the final chapter of this topic.

One warning before you write the second migration: adding a foreign key to a table that already contains orphan rows fails with Cannot add foreign key constraint. The safe order is always create tables first, then ALTER TABLE ... ADD CONSTRAINT once the data is clean. With the tables standing, the next question is what your DELETE statements are allowed to touch — the subject of the next chapter.

You type DELETE FROM products WHERE sku = 'A1' on a database where stock_levels and stock_adjustments already reference that row. What happens next is decided entirely by constraints you wrote months ago — and if you wrote none, MySQL's default answer is a surprise.

The default is not cascade. Without a foreign key, the delete succeeds and leaves orphan rows: stock quantities pointing at a product id that no longer exists, adjustments whose product_id can never be joined again. Every unit test on a fresh database passes, then the first monthly report joins products and silently drops those rows. Orphans are the failure mode that produces wrong numbers, not errors — which is why it survives review.

With a foreign key and no action specified, you get the opposite failure: the delete is rejected.

DELETE FROM products WHERE sku = 'A1';
-- ERROR 1451 (23000): Cannot delete or update a parent row:
-- a foreign key constraint fails (fk_stock_product)

That error is a feature when it guards history and a bug when it guards derived state. So each relationship gets an explicit rule:

-- migration 0002: attach the delete policy
ALTER TABLE stock_levels
    ADD CONSTRAINT fk_stock_product
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE;

ALTER TABLE stock_adjustments
    ADD CONSTRAINT fk_stock_product_history
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT;

-- optional reference: an author can be removed, their posts keep a null pointer
ALTER TABLE posts
    ADD CONSTRAINT fk_post_author
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL;

The pairing for this domain is the point of the chapter: cascade derived data, restrict recorded events. Current quantity can be recomputed from adjustments, so losing it costs nothing; the adjustment log is the audit trail, and a product deletion must not rewrite it. NO ACTION and RESTRICT differ only in deferred-constraint engines (PostgreSQL distinguishes them; MySQL treats them the same), so RESTRICT is the portable spelling.

Two follow-ups interviewers push on.

First, the application-logic alternative — "I'll delete children first in a transaction" — fails on concurrency: two requests both read the parent, one deletes children and parent, the other's child delete now targets a vanished parent. The constraint is enforced at the row lock, in the engine, on every path that touches the data — including the TRUNCATE-happy intern and the admin script you forgot about. Application checks are for turning that refusal into a friendly 409; they are not the enforcement mechanism.

Second, backfills. You cannot ADD CONSTRAINT on a table that already contains orphans — MySQL fails with Cannot add foreign key constraint. The repair is a cleanup statement you run before the ALTER, and it should be part of the migration:

DELETE sl FROM stock_levels sl
  LEFT JOIN products p ON p.id = sl.product_id
 WHERE p.id IS NULL;                      -- purge orphans, then add the constraint

Wrap both statements in one migration so no environment ends up half-constrained — that "which environments have the FK?" question is exactly what the schema_migrations tracker answers, which is where this topic ends: making up repeatable and the seed data worth running against. That is the next chapter.

Schema without data proves nothing: an empty table cannot fail pagination, cannot collide on a unique key, and cannot tell you whether 422 or 409 is what your API actually returns. The seed script is fixture design — you are choosing rows specifically so the behaviors from the acceptance list have something to trip over.

Design the dataset against AC-01…AC-07, not by habit:

Then make reruns boring. Seeding happens on every local reset, every fresh CI database, and every developer's second attempt of the day — so the script must be idempotent: run twice, same state, no crash, no duplicate rows.

<?php
// bin/seed.php — CLI, idempotent, safe to run anywhere
declare(strict_types=1);
$pdo = new PDO(getenv('DATABASE_URL'), null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$pdo->beginTransaction();
$ins = $pdo->prepare(
    'INSERT INTO products (sku, name, unit_price) VALUES (:sku, :name, :price)
     ON DUPLICATE KEY UPDATE name = VALUES(name), unit_price = VALUES(unit_price)'
);
foreach (fixtureProducts() as $p) {          // yields sku 'SKU-0001'..'SKU-0121'
    $ins->execute($p);
}
$pdo->commit();
printf("products: %d\n", $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn());

ON DUPLICATE KEY UPDATE keyed on the unique sku is what makes the second run a no-op-with-fresh-values instead of Integrity constraint violation. The alternative that also works: TRUNCATE and reinsert inside one transaction — but only when nothing references the rows yet (FOREIGN_KEY_CHECKS juggling is a sign the choice is wrong). Blind INSERT is never the answer.

The honest question is where seeds run. Keep two tiers: a small dev/demo seed (php bin/seed.php) humans run by hand, and per-test fixtures created in setUp() for PHPUnit — a test that depends on a seed file existing on the CI box is a test that fails on someone else's machine. Transaction-wrapped tests (roll back in tearDown()) or truncate-and-reseed both work; pick one and state it in the README.

Seeds need a schema, so both run through the migration runner, whose whole job is making up a no-op when there is nothing to do:

migrations/0001_create_core.sql
migrations/0002_add_constraints.sql
migrations/0003_add_indexes.sql

$ php bin/migrate.php status
  0001_create_core     applied 2026-03-02 09:14
  0002_add_constraints applied 2026-03-02 09:14
  0003_add_indexes     PENDING

$ php bin/migrate.php up && php bin/seed.php
applied 0003_add_indexes — schema up to date
products: 121

The runner's mechanics are thirty lines: a schema_migrations(version, applied_at) table created if missing, filenames compared against applied rows, each pending file executed and recorded inside one transaction with the DDL. That tracker is what makes a deploy safe to re-run, tells you instantly which environment drifted, and gives you the migration half of the rollback story in Implementing the REST Endpoints.

Two discipline rules finish the topic. Migrations are append-only — never edit an applied file, write 0004_... to fix it; environments differ precisely because someone "just tweaked" a file locally. And each migration must be safe against data written by the previous version, because that is the state production is in when the deploy runs.

With tables, constraints, and reproducible data in place, the API layer finally has something real to answer with.