#51 Denormalization/RDBMS
Yelp Review System
Businesses, reviews and ratings: find places near me, and show each one's average rating without recomputing it on every page.
What is being asked
Design a local business review site. The interesting parts are nearby search and how review data is stored so that reads, which dominate, stay cheap.
Requirements
Functional
- Search businesses by location, category and text.
- Read a business page with rating and reviews; write a review with photos.
Non-functional
- Reads outnumber writes by a hundred to one or more.
- The rating on a page may lag a new review by a few seconds.
- Reviews are valuable data: do not lose them.
Design
- A relational database holds businesses, users and reviews, with reviews indexed by business and date.
- Each business row carries denormalised fields: review count and rating sum (so the average is one division), and counts per star.
- Writing a review inserts the row; a pipeline then moderates it and updates the summary fields.
- Search uses an index with geographic filtering, fed from business changes, and stores the rating so results can be sorted without touching the database.
Key decisions and trade-offs
- Denormalise what is read constantly. Computing an average over ten thousand reviews on each page view is waste. Storing count and sum makes it constant-time and easy to keep correct when a review is edited or removed.
- Synchronous or asynchronous aggregate update. In the same transaction it is always right but couples the write to the hot business row. Asynchronously it scales better and is a little late.
- Relational first. The data is highly relational and the write volume modest; read replicas and a cache carry the reads long before sharding is needed.
- Trust. Filtering fake reviews and showing a recommended ordering matters more to the product than raw storage.
Follow-up questions
- How would you sort by "best match" combining distance and rating?
- How do you keep a business with three five-star reviews from outranking one with 4.7 over two thousand?
- How do you shard reviews if one database is no longer enough?
- How would you recompute every summary after a moderation sweep?