Design a warehouse inventory management database
PremiumDatabase designMedium
- Stock ledger
- Reservations
- Conditional update
Quick answer
This database design interview question asks you to design a warehouse inventory management database. 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 a warehouse inventory management database for an online retailer with 12 warehouses split into bins. Goods are received, put away, reserved for orders, picked and shipped. The hard parts are never overselling during flash sales and keeping an auditable history of every stock movement.
Each bin is a shelf location. Goods arrive on purchase orders and are put away into bins. When a customer orders, units are reserved, then picked from bins and shipped. Stock is also moved between warehouses and corrected after cycle counts. The storefront must always know how many units it can sell right now.
What must a warehouse inventory management database do?
- Available to sell: For a SKU, show units available per warehouse and in total (on hand minus reserved).
- Reserve: When an order is placed, reserve units at one warehouse; refuse if there are not enough; release the reservation after 30 minutes if payment fails.
How well must it do it?
- No overselling: Reserved units never exceed units on hand for a SKU at a warehouse, even when 500 orders hit one SKU in the same second during a flash sale.
- Auditable: Every quantity change writes a movement row; a balance must always equal the sum of its movements.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Stock ledger
- Reservations
- Conditional update
- Hot rows
- Lot tracking
- FEFO picking
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 a warehouse inventory management database 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?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team