I have a city review app and will let people review the cities where they have been. Forget about users for a while.

So in the db will look like this

CITIES:             REVIEWS:

| id | name     |   | id | CityId | score | 
|----|----------|   |----|--------|-------|
| 1  | Tokyo    |   | 1  | 1      | 3     |
| 2  | New York |   | 2  | 1      | 1     |
| 3  | Paris    |   | 3  | 3      | 5     |
                    | 4  | 3      | 2     |
                    | 5  | 3      | 1     |

My numbers:
I may have 1000 different cities and 10-50 reviews for each city (but reviews count will continue to grow)

A: Add a column averageReview to the CITIES table and update that average on every new review and simply pickup that value every time I have to retrieve a city

B: Calculate the average every time I have to retrieve a city

Which is the most common way to get the average review every time I need to retrieve a city?

  • 2
    A except you only update it once a day – Ewan Feb 4 at 11:05
  • Your numbers are small enough that you could calculate the average with every request. However, the timeliness of the average is not as important as the overall processing speed, so I agree with @Ewan. – Gilbert Le Blanc Feb 4 at 18:36
  • @Ewan: IMO, that's premature optimisation. Keep it simple for now, if later on calculating the review score becomes a major bottleneck, then think about caching/batching them. – Lie Ryan Feb 5 at 11:38
  • @LieRyan the more experience you get, the less premature optimisation seems. – Ewan Feb 5 at 12:04

In this case: Add a column “number of reviews” and a column “sum of reviews”. Then when a review is added, it’s a trivial change to the cities database that doesn’t require reading all the reviews for the city. Just add 1 to the count and the score to the sum of scores. And calculating the average is a single division.

|improve this answer|||||
  • Im with you, but to not modifi a table that could be "readonly" I would create a new table with 3 rows: CityId, ReviewsNumber and ReviewsSum (As example) – Balbu Feb 5 at 10:43
  • 1
    @Balbu I think it is safe to assume they can change the table unless mentioned otherwise. The OP actually specified changing the CITIES table themselves. – Jack Feb 5 at 11:26

Another option is to use a database trigger to calculate the average review score when a new review is added by a user, and store that calculated value in a column in your cities table. This way the score only needs to be calculated when the reviews for a city change, and will always be up to date, and its handled directly by the database rather than your code.

An alternative to the above is a stored procedure that your code calls which does the insert and calculation, rather than relying on the trigger.

Each DBMS is a bit different in how that is done exactly, but every major database server would support them.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.