I recently faced an interview question regarding the database design for storing user ratings on an e-commerce site expecting heavy traffic, like Amazon or AliExpress. The key requirements were to accurately show the average rating and total count for each product without making users wait for updates.
My initial idea was to store ratings in one column and maintain a count in another, where I'd calculate the average during write operations to optimize read performance. I also mentioned the possibility of queuing ratings for later processing during peak loads, but the interviewer was concerned that this would lead to delays for users seeing updated ratings.
What is a better solution for this scenario? Was my explanation lacking, or is there a preferable method that ensures real-time updates without sacrificing performance?
2 Answers
It sounds like the interviewer was trying to test your understanding of scalability rather than just table structure. You might consider a more distributed approach, where you write the ratings to both the database and an in-memory cache like Redis. This way, users instantly see the most up-to-date average. Also, investigate how caching strategies work; for example, using a time-to-live (TTL) that increases as the volume of ratings grows could help manage server load effectively.
That's a great question! What you're dealing with is a classic case of balancing real-time performance with database efficiency. One approach is actually similar to what Instagram did with likes; they introduced a total_like column to avoid constant recalculations. You could maintain fields for average rating and total count, then calculate the average only when needed. The caveat is handling vote changes, which can complicate things a bit. Overall, once you hit a critical mass of ratings (like 100 or more), users typically don’t expect immediate changes in their votes, so a little buffer time may not hurt.
Thanks for the suggestion! I guess I should emphasize how caching can alleviate immediate database load during busy times.