One price list for everybody
Three teams each keep their own orders. They all need the same price list. If each team copies it, the copies drift apart.
Better: keep one price list in a shared place and let every team link to it.
Key words
| Word | What it means |
|---|---|
| Repository | a shared place where the master data is kept |
| Link | a reference to data held somewhere else |
| Link test | a check that every link finds its target |
| Broken link | a link whose target does not exist |
| Dataset | a collection of related data about one subject |
| View | a saved search that shows fresh data each time |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can link a dataset to a repository and test that the link works.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Identify and review the datasets
Before linking, list each dataset and ask three things. What does it hold? What is its key, the value that matches rows? Is that key written the same way in both?
Here the key is product_id. Both datasets must use the same ids.
Manipulate the data into shape
Next, prepare the data you need. Keep only the columns you will use. Rename a column so both sides agree. Change a type, such as text to a whole number.
Related tables, such as orders and products, usually match on an id column.
Try it: link with a view
create view order_values as
select o.order_id, p.product, o.units * p.price_pence as pence
from team_orders o
join repo_products p on p.product_id = o.product_id;
select * from order_values order by order_id;
-- Order 103 is missing. Its product_id is 7.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.