Lesson 15 of 17 2 min

LLD Schema Design: Curated Practice Problems

Test your data modeling skills with 5 real-world schema design challenges.

Modeling for Persistence

In this practice session, focus on creating Entity-Relationship (ER) diagrams and SQL table structures for common systems.

Schema Modeling Challenges

1. Movie Booking System (BookMyShow)

  • Tables: Cities, Cinemas, Screens, Shows, Seats, Bookings.
  • Constraint: How do you store seat availability for different shows? (Avoid many-to-many junction tables if possible).

2. E-commerce Product Catalog

  • Tables: Categories, Products, ProductAttributes (EAV model vs JSONB).
  • Constraint: How do you support dynamic attributes (e.g., color for shirts, RAM size for laptops)?

3. Food Delivery (UberEats/Swiggy)

  • Tables: Restaurants, MenuItems, Orders, Drivers, Coupons.
  • Constraint: Model the relationship between an Order and multiple MenuItems with customizations.

4. Learning Management System (This Academy!)

  • Tables: Courses, Modules, Lessons, Users, UserProgress.
  • Constraint: Ensure efficient lookups for "What is the next uncompleted lesson for User X in Course Y?"

5. Multi-Tenant SaaS Billing

  • Tables: Tenants, SubscriptionPlans, Features, BillingHistory.
  • Constraint: Ensure one tenant cannot see another tenant's financial data.

Interview Reflector

For each schema you design:

  1. Normalization: Is it in 3rd Normal Form (3NF)?
  2. Indexing: Which columns need indexes for fast reads?
  3. Audit: Do we have created_at and updated_at columns for every table?

Final Takeaway

Don't just model for the "Happy Path." Model for the Queries. Think about the WHERE clauses you'll need to execute most frequently. 埋

Want to track your progress?

Sign in to save your progress, track completed lessons, and pick up where you left off.

Previous LLD Masterclass: The Observer Pattern in Production SystemsNext LessonLLD Lesson: The Decorator Pattern