DataslopeCase Study: Designing an Online Store
Apply relational design trade-offs to customers, products, categories, orders, and order line items.
An online store looks familiar from the outside: customers buy products. But the database design has to preserve details that matter later, especially order history.
This case study builds a store schema from requirements to tables.
Start with requirements
A small store needs to remember:
- A customer has an email and name.
- A product has a name, current price, and active flag.
- A category groups many products.
- A product can appear in many categories.
- A customer places orders.
- An order contains one or more products.
- Each order line stores quantity and the price charged at the time.
The phrase "an order contains products" hides a key design point. The relationship itself has attributes: quantity and price charged. That means it needs a table.
Find the entities and relationships
The core entities are customers, products, categories, and
orders. Two relationship tables complete the model:
product_categoriesconnects products and categories.order_itemsconnects orders and products with line-item facts.
Why is order_items more than just a technical join table?
It stores no facts of its own.
The relationship between an order and a product has its own attributes.
It replaces the need for an orders table.
It makes every product belong to one category.
Draw the full schema
The model has two many-to-many relationships, but they are different.
product_categories is a pure classification relationship.
order_items is a line-item table because it stores facts about each
purchased product.
Why copy unit_price?
products.current_price is the price now. order_items.unit_price is
the price charged then.
Copying the price onto the line item is a deliberate denormalization. It repeats a fact on purpose because order history must not change when a product's current price changes.
This is a design trade-off: a little duplication protects historical accuracy.
Create the schema
The query uses the relationships exactly as the model describes.
Test a design rule
The schema says an order line must have a positive quantity. That is a design rule, not just a user-interface rule.
Check your understanding
Why does orders have customer_id?
To copy the customer's email into every order.
To represent that each order is placed by one customer.
To make products unique.
To store the order total automatically.
What is the main reason order_items.unit_price exists?
PostgreSQL cannot join to products.current_price.
Past orders need the price charged at the time of purchase.
Every numeric column must be duplicated.
It replaces the quantity column.
Which table models the many-to-many relationship between products and categories?
orders
order_items
product_categories
customers