Design a library management system database
PremiumDatabase designEasy
- ER diagram
- Relational modeling
- Loans and holds
Quick answer
This database design interview question asks you to design a library 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 database for a library management system used by a city library network with 30 branches. Members borrow physical copies of books, renew them, place holds on books that are all checked out, and pay fines for late returns. The hard parts are that a copy is never lent twice and hold queues stay fair.
A book title may have many copies spread across branches, and each copy has its own barcode and condition.
What must a library management system database do?
- Check out: Lend one copy to one member; refuse if the member has 10 active loans, any overdue item, or unpaid fines above 5.
- Return: Mark the copy returned; if it is late, create a fine of 0.25 per day; if a hold exists on the book, set the copy aside for the first member in the queue for 3 days.
How well must it do it?
- One borrower per copy: The database must reject a second active loan for the same copy.
- Fair holds: Hold queue order is by the time the hold was placed and cannot be skipped.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- ER diagram
- Relational modeling
- Loans and holds
- Normalisation
- Soft deletes
- Constraints
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 library management system database and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Easy, so it suits engineers who are new to database design interviews. It is also a good warm-up before harder questions.
- 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