Exercise: One Shop, Five Shapes
Belongs to: NoSQL_Overview · Duration: ~30 min · Work in pairs · No computer needed (paper, whiteboard or a text editor)
You will store the same small shop in all five data models — and find out that every model makes some questions easy and others hard.
The shop data
Customers
| id | name | city |
|---|---|---|
| 1 | Anna | Krems |
| 2 | Ben | Tulln |
| 3 | Clara | Krems |
Products
| id | name | price |
|---|---|---|
| 1 | Keyboard | 40 |
| 2 | Mouse | 25 |
| 3 | Headphones | 80 |
Orders
| order | customer | items |
|---|---|---|
| 1001 | Anna | 1 × Keyboard, 2 × Mouse |
| 1002 | Ben | 1 × Headphones |
| 1003 | Anna | 1 × Headphones |
Friendships: Anna – Ben, Ben – Clara
1. Relational (5 min)
Write down the tables (name + columns + primary/foreign keys) you need for this data. No CREATE TABLE syntax needed, e.g. customer(id PK, name, city).
How many tables do you need? Which one stores the friendships?
2. Document (7 min)
Write the JSON documents for:
a) Order 1002 — with the customer and the items embedded. b) Customer Anna — with a list of her orders embedded (only order id and total).
Then answer: Anna moves to Linz. In how many places do you have to change her city in your design?
3. Key-value (5 min)
Design keys and values for:
a) Anna’s current shopping cart (she has 1 Mouse in it). b) A counter for how often product 3 was viewed. c) Ben’s login session, which should disappear after 30 minutes.
Use the naming convention type:id (e.g. cart:anna). Then answer: can your store tell you which customers have a Mouse in their cart? Why (not)?
4. Graph (8 min)
Draw the shop as a graph:
- one node per customer and per product (write the label, e.g.
:Customer, and the name), - a relationship FRIENDS_WITH for each friendship,
- a relationship BOUGHT from customer to product (put the quantity on the relationship as a property).
Tip: you may leave out the order nodes and connect customers directly to products.
Now answer only by following arrows on your drawing: Which products did Ben’s friends buy?
5. Wide-column (5 min)
In a wide-column store we want the query “all orders of one customer, newest first” to be as fast as possible.
Draw one row per customer (row key = customer name). Every order item becomes a column named orderId:product with the quantity as value. Example for Anna’s first column: 1001:Keyboard = 1.
How many columns does Anna’s row have? How many does Clara’s row have?
6. Which shape answers fastest? (no time limit — discuss)
Tick the model that answers each question most directly (several ticks allowed). Write one reason.
| Question | Relational | Document | Key-value | Graph | Wide-column | Reason |
|---|---|---|---|---|---|---|
| Show order 1001 with all items | ||||||
| How much money did customers from Krems spend in total? | ||||||
| Is Ben’s session still valid? | ||||||
| Which products did Ben’s friends buy? | ||||||
| All orders of Anna, newest first, 10 000 times per second |
Hand-in
- Your solutions for tasks 1–5 (photo of your paper is fine).
- The completed table from task 6.
- One sentence: Why is there no “best” data model?