Imagine the following scenario:

Article
- aid
- title
- body
- user_id

Votes
- vid
- rate
- user_id
- article_id

Votes keeps a list of tuples composed by the rate, the article being
voted and the user who is casting his/her vote. Assume that a vote is a
number between 0 and 10. I need this table, in order to make sure that
a user votes only once and also for statistics purposes. In my web
application, every time I display an article, I need to show what's the
average vote for that article.

>From a database design viewpoint, what's the best approach from the

followings?

1) Add the columns "sum_of_votes" and "number_of_votes" to the
'Article' table, in this way I won't have to query 'Votes' everytime I
need to just display an average value. I will just retrieve
sum_of_votes/number_of_votes to get the average. Everytime a vote is
cast, beside the insert into 'Votes' I will also perform an insert into
'Article' to update the values of "sum_of_votes" and "number_of_votes".

2) Keep the database structure as it is, and perform a query on 'Votes'
to retrieve the average of all the votes for the given article. 'Votes'
I assume, can become a pretty big table.

3) Other solution...

I often encounter a similar situation for average, sums, or similar
data that needs to be retrieved, and I wonder what's the best approach.
Please help me understand and learn from your expertise.

Thank-you.

lookmarge@gmail.com wrote:

> Imagine the following scenario:
>
> Article
> - aid
> - title
> - body
> - user_id
>
> Votes
> - vid
> - rate
> - user_id
> - article_id
>
> Votes keeps a list of tuples composed by the rate, the article being
> voted and the user who is casting his/her vote. Assume that a vote is a
> number between 0 and 10. I need this table, in order to make sure that
> a user votes only once and also for statistics purposes. In my web
> application, every time I display an article, I need to show what's the
> average vote for that article.
>
> > From a database design viewpoint, what's the best approach from the
>
> followings?
>
> 1) Add the columns "sum_of_votes" and "number_of_votes" to the
> 'Article' table, in this way I won't have to query 'Votes' everytime I
> need to just display an average value. I will just retrieve
> sum_of_votes/number_of_votes to get the average. Everytime a vote is
> cast, beside the insert into 'Votes' I will also perform an insert into
> 'Article' to update the values of "sum_of_votes" and "number_of_votes".
>
> 2) Keep the database structure as it is, and perform a query on 'Votes'
> to retrieve the average of all the votes for the given article. 'Votes'
> I assume, can become a pretty big table.
>
> 3) Other solution...
>
> I often encounter a similar situation for average, sums, or similar
> data that needs to be retrieved, and I wonder what's the best approach.
> Please help me understand and learn from your expertise.
>
> Thank-you.

Personally, I'd opt for solution #2. Less implementation, and less chance of error. As long as 'Votes' is indexed correctly, then there shouldn't be much of a performance hit. You will only ever be interested in the votes for a given article, so an index on this field should be sufficient for the query.

--