Design a portfolio management system database
PremiumDatabase designMedium
- Tax lots
- FIFO cost basis
- Corporate actions
Quick answer
This database design interview question asks you to design a portfolio management system 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 the architecture and database for a portfolio management system, like the wealth apps that show all of a family's investments in one place. It imports transactions from many brokers and fund houses, tracks holdings, cost basis and gains, and values portfolios daily. The hard parts are exact cost basis, corporate actions and fast history over billions of rows.
Users link brokerage, mutual fund and bond accounts. Transactions arrive through nightly file imports and API syncs, sometimes twice and sometimes corrected later. Users group accounts into portfolios such as "Retirement" or "Family" and see holdings, realized and unrealized gains, asset allocation and returns over time.
What must a portfolio management system database do?
- Holdings view: Show a portfolio's holdings with quantity, cost, current value and unrealized gain in under 200 ms.
- Import transactions: Accept buys, sells, dividends, fees, transfers and cash moves from many sources; a re-sent transaction is never counted twice.
How well must it do it?
- Exactness: Quantities to 8 decimal places and money to 4, as exact decimals; never floating point.
- Read speed: Dashboard and holdings queries under 200 ms at p99 at peak.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Tax lots
- FIFO cost basis
- Corporate actions
- Append only transactions
- Daily valuation snapshots
- Idempotent imports
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 portfolio management system 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