Design the database for an e-commerce store
FreeDatabase designMedium
- Database design
- Relational modeling
- Indexing
Quick answer
This database design interview question asks you to design the database for an e-commerce store. Aim for a design that stores data correctly and serves its main queries well, then explain the schema, keys, indexes and trade-offs. Interviewers want to see how you model entities, choose keys and design for the real access patterns.
The problem
Design the database for an online store that sells physical products. Customers browse products, place orders with several items, pay, and see their order history. The store tracks stock for every product variant (for example, a shirt in size M and colour blue) and must never sell more units than it has.
What scale should you design for?
Use these numbers in your estimates.
| Customers, 1 million products with 3 million variants (SKUs) | 10 million |
|---|---|
| Orders per day on average, with an average of 3 items per order; up to 2,000 orders per minute during sales | 50,000 |
| Constraint | Orders are kept for 5 years |
| Constraint | Product pages are read about 20,000 times per second at peak (mostly served from a cache in front of the database) |
Key concepts
- Database design
- Relational modeling
- Indexing
- Transactions
- Inventory consistency
How you’ll practise
- Access patterns
- Entities
- Schema
- Indexes and queries
- Scale and consistency
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any database design round.
- Lists the entities and how they relate before writing tables
- Designs the schema around the main access patterns
- Chooses primary keys, foreign keys and indexes with reasons
- Explains where to normalise and where to denormalise
- Considers integrity, transactions and concurrent writes
- Plans for growth: partitioning, archiving and migrations
Questions people ask about this interview question
- Which design type is this question?
- It is a database design question. You design the database for an e-commerce store and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Medium. Entry-level engineers can practise it, but it expects you to reason about requirements and trade-offs, not only list components.
- What should I cover first?
- Start with the entities and the main queries the database must answer. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- Yes. It is one of the free questions. Sign up to practise it step by step. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team