Designing a schema is a sequence of decisions, not an inspiration. Name the entities, give each a key, work out how they relate, choose types that match the data, and write down the rules the business insists on. This workshop takes one real requirement and walks it through those steps until there is a CREATE TABLE statement for every table.
What is the requirement, and what schema does it imply?
The tea shop has grown. It buys its products from partner roasters, and the business wants three things: to know which roaster supplied each product, to know how much of each product each roaster supplies for some products sold in partnership, and to be able to report on roaster performance. The requirement, in full:
- Each product has exactly one primary roaster, and may have additional partner roasters.
- A roaster has a name, a country, a founding year and an active flag.
- Each product records the date it was added to the catalogue and its stock level.
- Each order belongs to a customer and has a status and a shipping city.
Four entities fall out of that: products, roasters, orders, customers — plus the one relationship that is many-to-many.
Step one: entities and keys
Every entity becomes a table, and every entity needs a key. A roaster is identified by a roaster_id; a product by a product_id; a customer by a customer_id; an order by an order_id. All four are surrogate integer keys, because none of the natural candidates — the roaster's name, the product's SKU — is guaranteed to stay fixed or fit in a few bytes. The shop's existing four tables are already in place:
SELECT name FROM sqlite_master WHERE type = 'table' AND name IN ('customers','products','orders','order_items','roasters') ORDER BY name;
name
customers
order_items
orders
products
Five tables, and the fifth — roasters — is the new one this requirement calls for.
Step two: types and rules
Now write the table, deciding each column's type, its nullability and its default. The rules the business gave you map directly onto declarations:
CREATE TABLE roasters (
roaster_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT,
founded INTEGER,
active INTEGER NOT NULL DEFAULT 1
);
A roaster must have a name, so name is NOT NULL. The country and the founding year are optional, so they are nullable. The active flag is a boolean, which in SQLite is an INTEGER, and a new roaster starts active, so it carries DEFAULT 1. Note what is not declared: there is no check that founded is a plausible year and no uniqueness on the name, because the requirement did not say the names are unique. Declaring rules the business has not asked for is as much a mistake as failing to declare the ones it has.
Step three: the one-to-many relationship
"Each product has exactly one primary roaster" is a one-to-many relationship from roasters to products, so the foreign key goes on the products table. Adding it to a table that already exists is an ALTER TABLE — and adding a nullable column with a REFERENCES clause is one of the changes SQLite permits:
ALTER TABLE products ADD COLUMN roaster_id INTEGER REFERENCES roasters(roaster_id);
SELECT p.name, r.name AS roaster
FROM products p LEFT JOIN roasters r ON r.roaster_id = p.roaster_id
ORDER BY p.product_id LIMIT 5;
name roaster
-------------------- -------
Cast Iron Pan NULL
Enamel Mug NULL
Bamboo Tray NULL
Glass Carafe NULL
Walnut Serving Board NULL
Every product is listed and every roaster name is empty, because no product has been assigned a roaster yet. The LEFT JOIN is doing its job: it will keep showing the products as the assignments arrive. One caveat to note now rather than later — SQLite refuses to add a UNIQUE column to an existing table, with the message "Cannot add a UNIQUE column", so any rule that must exist from day one belongs in the CREATE TABLE statement rather than in a later migration.
Step four: the many-to-many relationship
"May have additional partner roasters" is the many-to-many case, and it needs a junction table with a composite primary key so that the same product and roaster cannot be paired twice:
CREATE TABLE product_roasters (
product_id INTEGER NOT NULL REFERENCES products(product_id),
roaster_id INTEGER NOT NULL REFERENCES roasters(roaster_id),
PRIMARY KEY (product_id, roaster_id)
);
cid name type notnull dflt_value pk
--- ---------- ------- ------- -------- --
0 product_id INTEGER 1 1
1 roaster_id INTEGER 1 2
Both columns are marked as part of the primary key, and both carry a foreign key back to their own table. Populating it makes the relationship real:
INSERT INTO product_roasters (product_id, roaster_id) VALUES (6, 1), (1, 1), (7, 1);
SELECT p.name, r.name AS roaster
FROM product_roasters pr
JOIN products p ON p.product_id = pr.product_id
JOIN roasters r ON r.roaster_id = pr.roaster_id
ORDER BY p.name;
name roaster
--------------- -----------
Cast Iron Pan Kettle & Co
Copper Kettle Kettle & Co
Linen Tea Towel Kettle & Co
Three products supplied in partnership by Kettle & Co. And the duplicate is refused, because the composite key says this pairing already exists:
UNIQUE constraint failed: product_roasters.product_id, product_roasters.roaster_id
Step five: review the design
Before writing the application, read the schema back against the requirement and check every clause. Does every entity have a key? Yes, all five tables have a surrogate primary key. Does every relationship have a foreign key? Yes: orders to customers, order_items to orders and products, products to roasters, and the junction table to both. Is anything stored twice? The roaster's name appears once, in the roasters table, and is referenced from two places. Is any rule missing? The requirement says a product has exactly one primary roaster — the column is nullable because the catalogue predates the roaster data, and making it NOT NULL would require backfilling every product first. That is a deliberate, recorded decision rather than an oversight.
Two additions would be worth arguing for at review time. A UNIQUE constraint on the product SKU already exists, so the natural key is protected. And an index on order_items.product_id would be worth adding, because every roaster-performance report groups by product — which is exactly the kind of decision module 10 makes properly, with a measured reason rather than a guess.
Quick reference
- Start from the entities in the requirement, and give every one a surrogate key.
- Map each business rule onto NOT NULL, DEFAULT, UNIQUE or CHECK — and no more than the business asked for.
- One-to-many puts the foreign key on the many side; many-to-many needs a junction table with a composite key.
- SQLite can add a nullable column with REFERENCES, but not a UNIQUE or PRIMARY KEY column.
- Read the finished schema back against the requirement before writing any code against it.
Workshop drill: the shop now wants to record, for each order, which roaster supplied each line — that is, the roaster attribution has to be captured at the moment of sale. Sketch the table that records it, say which existing table it relates to, and explain in one sentence why the roaster belongs on the order line rather than only on the product.