Ready to begin
No records have been processed. Start with Next step.
SECTION 02 / 15
One bill, many items: link two tables
On this page 2 headings
Shopping bills have a different shape. A bill has one shop, customer and total, but it can contain any number of items. Srivatsan’s first bill has seven items. Giving each item its own set of columns would make wide, uneven rows.
One large table is possible, but repeats details
One row per item gives a fixed set of columns, but repeats the shop, customer and bill total on every row. That wastes space and creates a trap: adding those repeated bill totals counts the same purchase several times.
Split fixed details from item details
| BillID | Shop | Customer | Total |
|---|---|---|---|
| 1 | SV Stores | Srivatsan | 567 |
| BillID | Item | Quantity | Price | Cost |
|---|---|---|---|---|
| 1 | Carrots | 1.5 | 50 | 75 |
| 1 | Soap | 4 | 32 | 128 |
| 1 | Tomatoes | 2 | 40 | 80 |
| 1 | Bananas | 8 | 8 | 64 |
| 1 | Socks | 3 | 56 | 168 |
| 1 | Curd | 0.5 | 32 | 16 |
| 1 | Milk | 1.5 | 24 | 36 |
REAL COURSE DATA · FOLLOW THE PROCEDURE
Link a bill to its item rows
Read the current record, check the explanation, and watch the variables. Use Back to revisit any step.
Ready
What we remember
- Processed
- 0
Start at the first step. Watch the current row and the values we remember.
BillID 1 appears once in the bill table and on all seven matching item rows. It connects the two tables. Reconstruct the bill by finding its fixed row and all item rows with that same ID. Use an identifier unique within the dataset; a customer can have several bills.
Quantity × Price = Cost for an item. Adding the seven Costs gives ₹567. Do not add the bill Total seven times. Every row in a particular table has the same column structure.
Try it yourself
Why not use Customer as the link?
Show explained answer
The same customer can have several bills. A unique BillID identifies the purchase.
Try it yourself
How many fixed-detail rows does Bill 1 need?
Show explained answer
One, even though it has seven item rows.
Try it yourself
What does adding ₹567 on every item row produce?
Show explained answer
₹3,969, which wrongly counts the same bill seven times.
Try it yourself
Can two different tables have different columns?
Show explained answer
Yes. The fixed-detail table and item table serve different purposes; each table has its own consistent columns.