Skip to content
Tutorials Center
My learning

Topics

Lesson 113 of 125 ยท Projects

Project: Inventory and Orders Database

Design an inventory and orders database from scratch. Model products, stock and orders, protect the data with constraints, and keep it consistent with transactions.

Most sample databases are handed to you ready-made. In this project you build one. You'll design the tables for a small shop that sells stationery, protect the data with constraints so that bad rows can't get in, keep stock counts correct with a trigger, and place orders inside transactions so that a failure never leaves half an order behind.

It is the best way to see how the earlier lessons fit together: keys and relationships shape the tables, constraints and foreign keys guard them, triggers automate bookkeeping and transactions keep multi-step changes atomic. Everything runs in your browser on SQLite.

The requirements

Write the requirements down before you write any SQL. Ours:

  • The shop sells products, each with a unique SKU, a name and a price.
  • It tracks how many units of each product are in stock, and the count must never go below zero.
  • Customers place orders. An order has several lines, one per product, each with a quantity.
  • A line stores the price at the time of the sale, because list prices change but old invoices must not.
  • Every change to stock is recorded in a ledger (stock_movements), so you can always explain why a count is what it is.
  • Placing an order is all-or-nothing: either every line is saved and stock is reduced, or nothing happens.

Stage 1: the design

From the requirements you can pick out the nouns (products, customers, orders, order lines, stock) and the relationships between them. A customer has many orders, an order has many lines, and each line points at one product. Product-to-order is therefore a many-to-many relationship, resolved by the order_lines table (see Relationships and ER Diagrams).

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_LINES : contains
  PRODUCTS ||--o{ ORDER_LINES : "appears in"
  PRODUCTS ||--|| STOCK : "has count"
  PRODUCTS ||--o{ STOCK_MOVEMENTS : "changes recorded in"

The inventory schema: order lines connect orders and products; stock and its ledger hang off products.

A few design decisions deserve a closer look:

  • Money is stored as integer cents (price_cents), not as REAL. Floating-point numbers can't represent 0.10 exactly, and tiny errors add up. Integers are exact. You divide by 100 only when displaying.
  • Stock lives in its own table, one row per product. Stock changes constantly while product details almost never do, and splitting them keeps the busy table small. It also lets a CHECK (on_hand >= 0) express the "never negative" rule directly.
  • order_lines has a composite primary key (order_id, product_id): one line per product per order. If a customer wants more, you raise the quantity.
  • unit_price_cents is copied into the line at sale time. This is deliberate denormalization of the "price at the time" fact, not a violation of it.

Stage 2: the schema and its guards

Every rule from the requirements becomes a constraint, so the database enforces it no matter which application connects:

Rule Enforced by
SKU is unique UNIQUE on products.sku
Names can't be blank CHECK (length(trim(name)) > 0)
Prices and stock can't be negative CHECK (price_cents >= 0), CHECK (on_hand >= 0)
Quantities are positive CHECK (quantity > 0)
Statuses are limited CHECK (status IN (...))
Orders belong to real customers FOREIGN KEY on orders.customer_id
Deleting an order removes its lines ON DELETE CASCADE on order_lines.order_id

The script also contains a trigger. After every insert into order_lines, it reduces the product's stock and writes a ledger row. Because the trigger runs inside the same statement, a line can never exist without its stock effect. One more detail: SQLite doesn't enforce foreign keys unless you ask, so the script starts with PRAGMA foreign_keys = ON;.

SQL Create the schema and seed the data

Press Run (or Ctrl + Enter) to see the result here.

The SELECT lists the three products with their stock (100 pens, 40 notebooks, 5 lamps). The last statement is a deliberate failure: the run stops with Error: UNIQUE constraint failed: products.sku. The duplicate never reaches the table, no matter what the application does. Change price_cents to -5 or the name to blank spaces and read the other messages.

The trigger body contains semicolons, so it ends with END; and not with the first semicolon. Database tools that split scripts at ; need special handling for triggers.

Stage 3: placing orders in transactions

An order touches several tables: one row in orders, several in order_lines, and (through the trigger) stock and stock_movements. If something fails halfway, you need all of it undone. That's what a transaction provides.

sequenceDiagram
  participant App
  participant DB
  App->>DB: BEGIN
  App->>DB: INSERT order
  App->>DB: INSERT line 1
  DB-->>DB: trigger: reduce stock, write ledger
  App->>DB: INSERT line 2
  alt every statement succeeded
    App->>DB: COMMIT
  else a statement failed
    App->>DB: ROLLBACK
  end

One order is one transaction: the order, its lines and the stock updates are saved together or not at all.

The script below first places an order and rolls it back, to prove that nothing sticks, then places two orders for real. It finishes with three reports, each one a query you will actually need:

  1. Order totals, computed from the stored line prices.
  2. A ledger reconciliation. For each product, starting stock plus the sum of movements must equal on_hand. When the check column shows 1, the ledger explains the stock exactly. In a real system you'd run this check on a schedule.
  3. A low-stock report.

Then it tries to oversell the last lamp.

SQL Orders, rollback, reports and an oversell

Press Run (or Ctrl + Enter) to see the result here.

Here is what the run shows:

  • Inside the first transaction, stock is 90 pens and 3 lamps. After ROLLBACK it is back to 100 and 5, and the new order and ledger rows are gone too.
  • After the two committed orders, the totals are 69.70 for Mira's order and 94.20 for Tomas's. Stock is 90 pens, 37 notebooks and 0 lamps (5 - 2 - 3).
  • The reconciliation shows ledger_matches = 1 for every product: the stock counts and the ledger tell the same story.
  • Only the lamp is below 5 units, with 0 on hand.
  • The final line fails with Error: CHECK constraint failed: on_hand >= 0. The trigger tried to take stock from 0 to -1 and the CHECK on stock refused. The insert into order_lines is undone with it.

That last point is subtle. The failing statement is atomic: the line was not saved. But the INSERT INTO orders before it was, and without the surrounding BEGIN you'd be left with an order header and no lines. In the script, the BEGIN is still open when the error stops the run, which is why the right application code is:

SQL
BEGIN;
INSERT INTO orders (...) VALUES (...);
INSERT INTO order_lines VALUES (...);   -- if this fails...
ROLLBACK;                               -- ...the application issues this

The database guarantees that the invalid state can't be committed; the application's job is to handle the error and roll back. Most database libraries wrap this in a try/commit/rollback-on-exception helper (see Connecting from Code).

Reading the stock first and then writing it ("check there is enough, then decrement") is a race condition when two customers order at the same time: both can pass the check. Letting the CHECK constraint reject the update, as here, is safe because the database checks it at the moment of the write. On PostgreSQL and MySQL, see Isolation and Locking for SELECT ... FOR UPDATE.

Stage 4: what the constraints protect against

Deleting data is where schemas often fail. The foreign keys protect you:

  • DELETE FROM customers WHERE id = 1 fails with FOREIGN KEY constraint failed as soon as customer 1 has an order, because the order would be left pointing at nobody.
  • Deleting an order removes its lines (ON DELETE CASCADE), because a line is meaningless without its order.
  • Deleting a product that appears on order lines fails, which is what you want: history must stay intact. Real systems add an active flag and hide discontinued products instead of deleting them.

Note the asymmetry in the design: cascading is used where the child cannot exist alone (lines), and the default restriction where the child is valuable history (orders of a customer).

Extending the project

Now make it yours:

  • Add a restock step that increases on_hand and writes a ledger row with the reason 'restock'. Which trigger would you attach, and to which table?
  • Add a UNIQUE constraint so that an email can't be registered twice in different capitalization (COLLATE NOCASE).
  • Write a trigger that refuses to add lines to an order whose status is shipped (see Triggers).
  • Create a view called order_totals so reports don't repeat the join.
  • Add an index to speed up "all orders of a customer" and check the plan with EXPLAIN QUERY PLAN.
  • Write a schema_migrations entry for each change, as described in Schema Migrations and Zero-Downtime Changes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the schema store prices as integer cents instead of REAL?

    Floating-point values are approximations, and the rounding errors accumulate when you add many prices. Integer cents are exact; you divide by 100 only for display.
  2. 2. Why is unit_price_cents copied into order_lines instead of reading the price from products when needed?

    Historical orders have to keep the price in effect at the time of the sale. Copying it into the line is a deliberate, documented duplication.
  3. 3. An order line insert makes the trigger reduce stock below zero. What happens?

    The trigger runs in the same statement as the insert. Its UPDATE violates CHECK (on_hand >= 0), so the whole statement is undone and the error is reported.
  4. 4. Why wrap the order header and all its lines in one transaction?

    An order spans several statements. Without a transaction, a failure halfway can leave an order with missing lines or stock reduced for an order that was never saved.

Summary

  • Start from written requirements; nouns become tables and relationships become keys. Many-to-many relationships need a linking table such as order_lines.
  • Store money as integer cents, and copy the sale price into the line so history stays correct.
  • Turn every business rule into a constraint (UNIQUE, CHECK, FOREIGN KEY, NOT NULL), so that no application can break it.
  • Use a trigger when a change must always come with a side effect, such as reducing stock and writing a ledger row.
  • Wrap multi-statement changes in a transaction, and ROLLBACK when anything fails.
  • Reconcile derived numbers against their source (stock versus ledger) to catch bugs early.

Next: SQL Cheat Sheet, a one-page summary of the statements you have been using.