Don't Store It Twice

For this lesson, every product also carries its supplier's name and phone. PaperCo makes three products, so its phone is written three times:

name | supplier | supplier_phone
Mug | Clayworks | 0721 111 222
Notebook | PaperCo | 0722 333 444
Lamp | Bright Home | 0723 555 666
Pen | PaperCo | 0722 333 444
Plant | Bright Home | 0723 555 666
Teapot | Clayworks | 0721 111 222
Stapler | PaperCo | 0722 333 444
Gift card | GiftPal | 0724 777 888

When PaperCo's number changes, every copy must change, and one missed copy leaves the table disagreeing with itself. The cure is to store it once. Move the repeated facts to their own table, give each row an id, and keep only that id on the products. A join brings the name and phone back whenever you need them.

Moving the data takes a few statements in a row: create the new table, fill it, add the id column, fill that, and drop the old columns. An INSERT can take its rows from a SELECT instead of VALUES, and DISTINCT keeps each value once. For categories it would look like this:

INSERT INTO categories (name)
SELECT DISTINCT category FROM products;

ALTER TABLE … ADD COLUMN and ALTER TABLE … DROP COLUMN change a table that already has rows. An UPDATE can set a column from a subquery, row by row. query holds all the moving statements, each ending with a semicolon, and fix changes PaperCo's phone. The check runs query, shows both tables, runs fix and joins the tables back together. It must print:

id | name | phone
1 | Clayworks | 0721 111 222
2 | PaperCo | 0722 333 444
3 | Bright Home | 0723 555 666
4 | GiftPal | 0724 777 888
(4 rows)
id | name | price | category | stock | supplier_id
1 | Mug | 12 | kitchen | 10 | 1
2 | Notebook | 7 | office | 0 | 2
3 | Lamp | 29 | home | 1 | 3
4 | Pen | 2 | office | 120 | 2
5 | Plant | 15 | home | 6 | 3
6 | Teapot | 22 | kitchen | 4 | 1
7 | Stapler | 9 | office | 15 | 2
8 | Gift card | 50 | NULL | 999 | 4
(8 rows)
Rows the fix changed: 1
name | supplier | phone
Mug | Clayworks | 0721 111 222
Notebook | PaperCo | 0722 999 000
Lamp | Bright Home | 0723 555 666
Pen | PaperCo | 0722 999 000
Plant | Bright Home | 0723 555 666
Teapot | Clayworks | 0721 111 222
Stapler | PaperCo | 0722 999 000
Gift card | GiftPal | 0724 777 888
(8 rows)

Your task: move the suppliers out of products into a new suppliers table and link each product to its supplier with supplier_id. Then drop the copied columns and change PaperCo's phone to 0722 999 000 with one UPDATE.

Assignment
Follow the Coding Tutorial to build suppliers, link the products to it and fix PaperCo's phone in one place.

Hint
Drop the old columns last. The UPDATE that fills supplier_id still needs products.supplier to find each supplier's id.