Design the database for a stock trading app
PremiumDatabase designHard
- Order lifecycle
- Position ledger
- Idempotent fills
Quick answer
This database design interview question asks you to design the database for a stock trading app. 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 a stock trading app run by a retail brokerage. Customers deposit cash, place market and limit orders, see them fill partially or fully, hold positions and track profit and loss. The hard parts are exact balances, duplicate fills and keeping every record unaltered for 7 years.
Profit and loss is shown against live prices. Trades settle one business day later (T+1). Regulators require every order event and every balance change to be kept, unaltered, for 7 years. The exchange's matching engine is external; you receive acknowledgements and fills from it.
What must the database for a stock trading app do?
- Place an order: Buy or sell, market or limit, for a quantity; reserve the cash (buy) or the shares (sell) so the customer cannot spend them twice while the order is open.
- Order lifecycle: New, acknowledged, partially filled, filled, cancelled, rejected, expired, with every event from the exchange recorded in order.
How well must it do it?
- Exactness: Cash and share quantities are exact; fractional shares to 6 decimal places.
- Idempotent fills: A duplicated fill from the exchange must be applied once.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Order lifecycle
- Position ledger
- Idempotent fills
- T+1 settlement
- Append only
- Time series
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 a stock trading app and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Hard. It suits engineers preparing for senior interviews, where you are expected to drive the discussion and go deep on trade-offs.
- 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