The items live in another table

01 / 04

No names in the orders table

Look at orders and you cannot tell what was sold. All that is in there is item_id — a number and nothing else.

Being told "one", "three", "two" does not tell you whether that is a chopstick rest or a mug, does it.

SELECT id, item_id, quantity  FROM orders  LIMIT 3;

Result

1 | 1 | 2
2 | 3 | 4
3 | 2 | 4

02 / 04

The items table

The information about the goods is in the items table, one record per item.

id … the item number
name … the item name
price … the price
category … what kind it is

SELECT * FROM items;

Result

1 | cypress chopstick rest | 480 | table
2 | sunrise mug | 1600 | table
3 | cotton dishcloth | 340 | kitchen
4 | dappled light lamp | 3800 | lighting
5 | bean coaster | 260 | table
6 | moonlit vase | 2400 | decor

03 / 04

Why split them at all

You will be thinking "why not just write the name and the price on every order?". But there is a lot to be gained from keeping them apart.

you do not write the same item name over and over
when a price changes you fix it in one place
no misspelled variations creep in

Splitting tables up by their job like this is the ordinary state of a database.

SELECT name, price FROM items  ORDER BY price DESC  LIMIT 3;

Result

dappled light lamp | 3800
moonlit vase | 2400
sunrise mug | 1600

04 / 04

For now you read them one at a time

Every tool you know works on items as it is. You can narrow with WHERE, gather with GROUP BY, name things with AS.

The joining is still to come. This section is for practising reading two tables one at a time; from the next one you go looking for the key. Let us write some.

SELECT category, COUNT(*) AS kinds  FROM items  GROUP BY category  ORDER BY category;

Result

decor | 1
kitchen | 1
lighting | 1
table | 3