Design a Food Review System for a Delivery App, Focused on the Database Schema

Design the food review system of a food-delivery platform. After an order is delivered, the customer can review the food they received, and other customers read reviews and ratings when they decide what to order. Cover the system end to end, but expect most of the discussion to be about the database schema: the entities and tables, their keys and relationships, the constraints that keep the data valid, and how the main reads and writes use the schema. In the reported interview, scaling was left largely to the candidate's judgment, and most of the interviewer's questions were about how to design the schema. ```hint Anchor the review to the order Decide which existing record proves that a customer actually received the food, and let the review's keys and uniqueness constraints follow from it. ``` ```hint Let the screens pick the indexes List the screens that show reviews and ratings. Check that each one is served by an index or a precomputed row rather than by aggregating every review on each request. ``` ### Constraints and Clarifications - No scale, latency or product limits were given. State the numbers and rules you assume. - Assume the platform already stores customers, restaurants, menu items, orders and order items; the review system builds on them. ### Clarifying Questions - What is reviewed: the restaurant as a whole, the individual dishes in the order, or both? Is the courier's delivery part of the same review? - Who may review: only the customer whose order was delivered? Once per order, or once per dish? Is there a deadline after delivery? - Can a review be edited or deleted, and must earlier versions be kept? - Do reviews include photos? Are they moderated before they appear? - What do readers see: an average rating and count per restaurant and per dish, the newest reviews, the most helpful ones? Can the restaurant reply? - How soon after a review is posted must it show up in averages and lists? ### What a Strong Answer Covers - Settles what is reviewed, who may review, and the edit, delete and moderation rules before drawing tables - Tables with primary and foreign keys, data types, nullability, and constraints that enforce one review per eligible order or item - Indexes matched to each read path, with pagination that stays fast on deep pages - Rating aggregates that stay correct through creates, edits, deletes and moderation decisions - APIs, the write path, photo storage, and a brief scaling plan grounded in the read and write volumes ### Follow-up Questions - A customer changes a 5-star review to 1 star. Walk through every row that changes and how the restaurant's average stays correct. - A restaurant renames a dish, then removes it from the menu. What happens to its reviews and its rating? - The product adds "most helpful" sorting based on votes from other customers. What changes in the schema and the read path? - How would you detect fake or abusive reviews, and where does that state live in the schema?