The shop

Problem

Who ordered what?

The shop owner asks: “What was in the last order, and whose was it?”

In Excel there would be one row with everything. In the database the answer is spread over three tables. That sounds like a complication, but it is the opposite. Let us see why.

The idea

Six tables

The shop has six tables. Each one is for one kind of thing:

customers

• PKidinteger
• nametext *
• emailtext *
• citytext
• joined_attext *

categories

• PKidinteger
• nametext *
• FKparent_idinteger→ categories

products

• PKidinteger
• nametext *
• FKcategory_idinteger→ categories
• pricereal *
• stockinteger *
• added_attext *

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *
• statustext *

order_items

• PKorder_idinteger→ orders
• PKproduct_idinteger→ products
• quantityinteger *
• unit_pricereal *

reviews

• PKidinteger
• FKproduct_idinteger *→ products
• FKcustomer_idinteger *→ customers
• ratinginteger *
• bodytext
• created_attext *

• customers: the customers. categories: the product categories. products: the products, with a price in euro and the stock.
• orders: the orders. An order belongs to one customer and has a date and a status: new, paid, shipped, delivered or cancelled.
• order_items: what is in each order. One row is one product in one order.
• reviews: the reviews, with a rating from 1 to 5.

PK is the column a row is known by: the number id. FK is a column that points to a row in another table. The asterisk means the column cannot be empty.

More precisely, if you are curious

Prices are REAL, fractional numbers. Dates are text like 2026-10-19: SQLite has no separate date type, and in this form the text sorts correctly. PostgreSQL and MySQL have DATE and NUMERIC types, and there you use those. We will come back to types when we make our own tables.

See it

The last order, row by row

The last order is 60. In orders it has customer_id 1. Customer 1 in customers is Maria Ivanova.

What did she order? The rows of order 60 are in order_items. Run the table and scroll to the end:

SQLdatabase: shop

SELECT * FROM order_items;

SELECT * FROM order_items;

▶ RunRestore the queryFresh database

The last two rows belong to order 60: product 1, 2 pieces at 1.20 €, and product 19, 4 pieces at 2.90 €. Now see which products 1 and 19 are:

SQLdatabase: shop

SELECT * FROM products;

SELECT * FROM products;

▶ RunRestore the queryFresh database

White bread and dish soap. The answer was put together from three tables, and each fact is stored only once: Maria's name, the bread's name, its price.

More precisely, if you are curious

Searching 152 rows by eye is slow and easy to get wrong. In the next module you will tell the database “only the rows of order 60”, and later you will bring the three tables together in one query with JOIN.

Try it

Whose review?

1. Review 3 is for product 17. Which product is that? (Run the products above and look for id 17.)

Coffee beansFrying pan 28 cmKashkaval cheese

Check

Exercise

The reviews

Show all reviews (table reviews). Which review has the lowest rating, and which product is it for?

SQLdatabase: shop

SELECT * FROM products;

SELECT * FROM products;

Check▶ Run

Test

Check yourself

1. How many times is the name of product 1, “White bread”, stored in the database?

Once, in productsAs many times as it has been orderedNever: only the number 1 is stored

2. Why does order_items keep unit_price instead of taking the price from products?

Because the price in products can change, and the order must remember the price on the day of purchaseTo make it fasterA design mistake

3. Order 60 has two rows in order_items. What does that mean?

The order was paid in two partsThe order has two different productsThe order was entered twice

Check

Start the lesson over