Designing a schema is a sequence of decisions, not an inspiration. You name the entities, give each one a key, work out how they relate, choose the 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.
The previous lesson in this module covered "How Does Normalization Remove Duplication?", and this lesson is the last one in the Table Design and Schema module.
What is the requirement, and what schema does it imply?
The tea shop has grown. It buys its products from the partner roasters, and the business wants three things. It wants to know which roaster supplied each product, it wants to know how much of each product each roaster supplies for the products that are sold in partnership, and it wants to be able to report on roaster performance.
The requirement, in full.
- Each product has exactly one primary roaster, and it 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: the products, the roasters, the orders and the customers, plus one relationship, the many-to-many.
Step one: the entities and the 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, and an order by an order_id.
All four are surrogate integer keys, because none of the natural candidates, which are the name of the roaster and the SKU of the product, is guaranteed to stay fixed or to fit in a few bytes.
The shop's four existing 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
productsThere are five tables, and the fifth, which is roasters, is the new one this requirement calls for.
Step two: the types and the rules
Now you write the table, and you decide each column's type, its nullability and its default.
The rules the business gave you map directly onto the 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 has to have a name, so name is NOT NULL. Country and founding year are the optional ones, 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.
Notice what is not declared. There is no check that founded is a plausible year, and there is no uniqueness on the name, because the requirement did not say that the names are unique.
Declaring rules the business has not asked for is as much a mistake as failing to declare the ones that 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 that 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 NULLEvery 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.
There is 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 has to 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 the additional partner roasters" is the many-to-many case, and it needs a junction table with a composite primary key, so the same product and the same 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 2Both 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 & CoThree products are supplied in the partnership by Kettle & Co. And the duplicate is refused, because the composite key says that this pairing already exists.
UNIQUE constraint failed: product_roasters.product_id, product_roasters.roaster_idStep five: review the design
Before you write the application, you read the schema back against the requirement, and you 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. The orders to the customers, the order_items to the orders and to the products, the products to the roasters, and the junction table to both of them.
Is anything stored twice? The name of the roaster appears once, in the roasters table, and it is referenced from two places.
Is any rule missing? The requirement says that 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 the backfilling of every product first. That is a deliberate and 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 the product. That is exactly the kind of decision that 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 of them a surrogate key.
- Map each business rule onto
NOT NULL,DEFAULT,UNIQUEorCHECK, and no more than the business asked for. - One-to-many puts the foreign key on the many side, and many-to-many needs a junction table with a composite key.
- SQLite can add a nullable column with a
REFERENCESclause, but it cannot add aUNIQUEor aPRIMARY KEYcolumn. - Read the finished schema back against the requirement before you write any code against it.
Workshop drill: the shop now wants to record, for each order, which roaster supplied each of the lines. In other words, the roaster attribution has to be captured at the moment of the sale. Sketch the table that records it, say which existing tables it relates to, and explain in one sentence why the roaster belongs on the order line and not only on the product.