One café, four databases skills
This combined project brings Databases, PostgreSQL, MongoDB and Prisma together around one café's orders. You'll design the tables, ask real business questions in SQL, use PostgreSQL's power tools, model the same data as documents, and finally drive it all from typed application code.
- Day 1: schema design (SQL)
- Day 2: the sales report (joins, groups, window functions)
- Day 3: index, view and a transaction (PostgreSQL)
- Day 4: the same orders as documents (MongoDB)
- Day 5: the app's data layer (Prisma)
Let the database say no
Constraints are rules the database enforces for *every* program that writes to it: keys identify rows, foreign keys keep relations valid, CHECKs reject impossible values.
CREATE TABLE customers (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
city text NOT NULL
);
CREATE TABLE dishes (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE,
price int NOT NULL CHECK (price > 0),
stock int NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE orders (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id int NOT NULL REFERENCES customers (id),
status text NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'served', 'cancelled')),
placed_on date NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE order_items (
order_id int NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
dish_id int NOT NULL REFERENCES dishes (id),
qty int NOT NULL CHECK (qty BETWEEN 1 AND 20),
PRIMARY KEY (order_id, dish_id)
);