Projects›Core· 120 min read

Project: Design & Query a Database

Design a real multi-table database and write the queries an app would need.

What you will learn

Design normalized tables with keys
Populate with data
Write real-world queries

The brief

Choose an app (a blog, shop, or school) and build its database, then write the queries it would use.

At least two related tables with primary and foreign keys.
Sample data inserted into each.
Queries: list all, filter with WHERE, sort with ORDER BY, search with LIKE.
At least one JOIN combining the tables.
One aggregate query (COUNT or AVG with GROUP BY).

Build it step by step

Not sure where to start? Follow these steps in order — each one uses a command you have already learned, so you can flip back to that lesson if you get stuck:

Pick your app and jot the tables on paper. For a shop you might have customers and orders; for a blog, authors and posts. Decide which table the foreign key lives in (the “many” side — an order belongs to a customer).
Create the database with CREATE DATABASE myshop; then USE myshop; so the next commands run inside it.
Create the first (parent) table — e.g. customers — giving it an id INT AUTO_INCREMENT PRIMARY KEY plus its own columns.
Create the second (child) table — e.g. orders — with its own id, a customer_id INT column, and a FOREIGN KEY (customer_id) REFERENCES customers(id) to link them.
Insert sample rows into the parent table first (so the ids exist), then into the child table, pointing each child row at a real parent id with INSERT INTO.
Write a plain read with SELECT * FROM customers; to confirm your data is there.
Filter, sort and search: one WHERE query, one ORDER BY query, and one LIKE search.
Join the tables with INNER JOIN to answer a real question, like listing each order next to its customer’s name.
Summarise with one aggregate query — for example SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id; to count orders per customer.

Note: Worked mini-example of the flow: CREATE DATABASE myshop; USE myshop; CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100)); CREATE TABLE orders (id INT AUTOINCREMENT PRIMARY KEY, customerid INT, total DECIMAL(8,2), FOREIGN KEY (customer_id) REFERENCES customers(id)); INSERT INTO customers (name) VALUES ('Asha'), ('Ravi'); INSERT INTO orders (customer_id, total) VALUES (1, 500), (2, 250), (1, 900); SELECT customers.name, orders.total FROM orders INNER JOIN customers ON orders.customer_id = customers.id; That last line prints each order’s total next to the customer who placed it — Asha 500, Ravi 250, Asha 900 — proving the whole design works end to end.

Tip: This database is exactly what your PHP or Laravel app will sit on top of. Get the design right and the app becomes easy.

✍️ Practice

Build the database and run all the required query types.
Write a JOIN that answers a real question (e.g. “orders with customer names”).

🏠 Homework

Document your schema (tables, columns, relationships) in a short note.