The bakery, stage 1: the database and the tables
In short: Stage 1 of the project goes from the description of a local bakery to the table plan and five CREATE TABLE statements with the constraints that keep the data correct.
Problem
The bakery on the corner
A neighbourhood bakery wants to take orders online. It sells bread, pastries and drinks. Customers make a profile with a name, an e-mail and a city. An order has a date, a status (new, paid, delivered, cancelled) and a few rows: which product, how many pieces and at what price. The bakery also wants to know how much of each product is in stock.
In this project you build the whole database yourself: from an empty database, through the tables, the data, the queries and the changes, to the backup and DROP at the end. Every stage starts from what you made in the previous ones.
The idea
The plan
Before the first CREATE TABLE you decide what things there are and how they are connected. Here there are five:
| Table | What it keeps | Points to |
|---|---|---|
categories |
the categories: bread, pastries, drinks | |
products |
the products with price and stock | categories |
customers |
the customers with e-mail and city | |
orders |
the orders with date and status | customers |
order_items |
the rows of the orders | orders and products |
Every table has id INTEGER PRIMARY KEY, except order_items: there the key is the pair of order and product, because one product is one row in the order. The tables that are pointed to are made first.
The idea
The empty database
On your computer the database is made with sqlite3 bakery.db (in PostgreSQL with CREATE DATABASE bakery; and \c bakery). The fields in this project start from an empty database, without the shop from the other lessons:
Try it
The categories
Make the table categories: id is the primary key, and name is required and cannot repeat (there must not be two categories "Bread").
Solution
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);name TEXT NOT NULL UNIQUE. The check tries to write two categories "Bread" and one without a name and counts how many went in: it must be one. Without UNIQUE two go in, and without NOT NULL the category without a name too.
See it
Once more
A table with the same name cannot be made again:
A script that can be run more than once writes IF NOT EXISTS. Then the existing table is not touched, even if it has other columns:
Try it
The products
Add the rules of products: name is required, category_id is required and points to categories, price is required and greater than 0, and stock is required, 0 by default and cannot be below 0.
Solution
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category_id INTEGER NOT NULL REFERENCES categories(id),
price REAL NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);category_id INTEGER NOT NULL REFERENCES categories(id), price REAL NOT NULL CHECK (price > 0) and stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0). The check tries a product with price 0, with stock -1 and without a name, and all of them must be stopped. A product without stock must get 0.
More precisely, if you are curious
The prices here are REAL, as in the course's shop. A real program for money keeps cents as a whole number (price_cents INTEGER) or uses NUMERIC(10, 2) in PostgreSQL, so that there are no sums like 0.1 + 0.2 = 0.30000000000000004.
Try it
The customers
Add the rules of customers: name and email are required, email cannot repeat, and city may be missing.
Solution
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT
);email TEXT NOT NULL UNIQUE, and city TEXT without NOT NULL. The e-mail is what the customer logs in with, and two people with one e-mail are a mistake. The city is not required: a customer without a city can still order.
Try it
The orders
Add the rules of orders: customer_id is required and points to customers, ordered_at is required, and status is required, 'new' by default and can only be 'new', 'paid', 'delivered' or 'cancelled'.
Solution
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
ordered_at TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'new'
CHECK (status IN ('new', 'paid', 'delivered', 'cancelled'))
);status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'paid', 'delivered', 'cancelled')). The check tries the status 'lost' and an order without a date, and an order without a status must become 'new'.
Exercise
The rows of the order
Add the rules of order_items: order_id points to orders, and when the order is deleted its rows are deleted with it; product_id points to products; quantity is required and greater than 0; unit_price is required; the primary key is the pair (order_id, product_id). All the columns are required.
Solution
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price REAL NOT NULL,
PRIMARY KEY (order_id, product_id)
);REFERENCES orders(id) ON DELETE CASCADE and at the end PRIMARY KEY (order_id, product_id). The check writes the same product twice in the order (the second must be stopped by the key), a row with 0 pieces, and then deletes the order and checks that its rows are gone. With a separate id for the row, one product could be two rows in one order.
The idea
The five tables
The database is ready:
Every rule you added is a mistake that no program will be able to write: a product without a price, two customers with one e-mail, an order with a status that does not exist. The next stage fills the tables.
Test