A road map, a shop and a company
You must store three things: a map of roads, the orders of an online shop, and the staff of a company.
Do you use the same shape for all three? No. A map has many roads meeting at many towns. A company has one boss at the top. A shop has customers, orders and products that all link.
This lesson helps you choose.
Key words
| Word | What it means | Example |
|---|---|---|
| Hierarchical model | A tree. Every item has one parent (one item above it). | A company chart |
| Network model | A web. An item can link to many others. | A road map |
| Relational model | Tables linked by keys. | Customers and orders |
| Efficient | Quick, or small, or cheap. It uses little time or space. | Finding a row in a second |
| Storage | The space the data takes on a disk. | 3000 characters |
| Complexity | How hard something is to build and understand. | A model with many links |
| Index | A list that helps the computer find a row fast. | Like the index of a book |
| Key | An id in a row that links it to a row in another table. | customer_id |
| Duplicate | The same fact written more than once. | One address in 100 rows |
The cells students fill in are left blank in this preview.
The three models side by side
| Model | Finding one item | Storage | Building it | Best for |
|---|---|---|---|---|
| Hierarchical | Fast if you start at the top and go down | Good, but a shared item is copied | Easy | Folders, company charts |
| Network | Fast along the links | Good, nothing copied | Hard: many links to plan | Maps, where many items link to many items |
| Relational | Fast with keys and an index | Good: each fact stored once | Medium: tables and keys | Shops, schools, banks |
The cells students fill in are left blank in this preview.
Quick check: which model?
Have a go
Quick check: which model?
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Factor 1: finding one item
How fast can the computer find one item?
In a tree you start at the top and go down. This is fast if you know the path. It is slow if you do not. In a relational model the computer uses a key and an index. An index is like the index of a book: it jumps to the right place.
Factor 2: storage
How much space does the data need?
If the same address is written in 100 rows, that is wasted space. It is also a risk. If the address changes, you must change 100 rows. A relational model stores the address once and points to it with a key.
Worked example: one address, 100 orders
| Way | What is stored | Characters used |
|---|---|---|
| One flat table | 100 orders, each with the 30-character address | 100 times 30 = 3000 |
| Two linked tables | 1 address of 30 characters + 100 customer ids of 3 characters | 30 + 300 = 330 (100 times 3 is 300) |
| Saving | 3000 take away 330 | 2670 characters saved |
| If the address changes | Flat: 100 rows to change. Linked: 1 row to change. | 100 changes against 1 change |
The cells students fill in are left blank in this preview.
Try it: count the characters
orders = 100
address_size = 30
id_size = 3
flat = orders * address_size
linked = address_size + orders * id_size
print("Flat:", flat)
print("Linked:", linked)
print("Saved:", flat - linked)
# Change orders to 10 and run it again. Is the saving still big?