How do I optimise my voting application to produce monthly charts?

I'd appreciate any help you can offer - I'm currently trying to decide on a schema for a voting app I'm building with PHP / MySQL, but I'm completely stuck on how to optimise it. The key elements are to allow only one vote per user per item, and be able to build a chart detailing the top items of the month – based on votes received that month.

So far the initial schema is:

Items_table
 item_id
 total_points
 (lots of other fields unrelated to voting)

Voting_table
 voting_id
 item_id
 user_id
 vote (1 = up; 0 = down)
 month_cast
 year_cast

So I'm wondering if it's going to be a case of selecting all information from voting table where month = currentMonth & year = currentYear, somehow running a count and grouping by item_id; if so, how would I go about doing so? Or would I be better off creating a separate table for monthly charts which is updated with each vote, but then should I be concerned with the requirement to update 3 database tables per vote?

I'm not particularly c开发者_如何学JAVAompetent – if it shows – so would really love any help / guidance someone could provide.

Thanks,

_just_me

I wouldn't add separate tables for monthly charts; to prevent users from casting more than one vote per item, you could use a unique key on voting_table(item_id, user_id). As for the summary, you should be able to use a simple query like

select item_id, vote, count(*), month, year 
from voting_table 
group by item_id, vote, month, year

I would use a voting table similar to this:

create table votes(
  item_id
 ,user_id
 ,vote_dtm
 ,vote
 ,primary key(item_id, user_id)
 ,foreign key(item_id) references item(item_id)
 ,foreign key(user_id) references users(user_id)
)Engine=InnoDB;

Using a composite key on a innodb table will cluster the data around the items, making it much faster to find the votes related to an item. I added a column vote_dtm which would hold the timestamp for when the user voted.

Then I would create one or several views, used for reporting purposes.

create view votes_monthly as
select item_id
      ,year(vote_dtm)  as year
      ,month(vote_dtm) as month
      ,sum(vote) as score
      ,count(*)  as num_votes
  from votes
 group
    by item_id
      ,year(vote_dtm)
      ,month(vote_dtm);

If you start having performance issues, you can replace the view with a table containing pre-computed values without even touching the reporting code.

Note that I used both count(*) and sum(vote). The count(*) would return the number of cast votes, whereas the sum would return the number of up-votes. Howver, if you changed the vote column to use +1 for upvotes and -1 for downvotes, a sum(vote) would return a score much like the votes on stackoverflow are calculated.