Hey everyone,

My music site does a yearly "top 30 records" list. In previous years,
each dozen or so writers post a list numbered 1-30 of their top
records. Each one is assigned a score (eg, their #1 gets 30 points, #2
gets 29, etc) and we add up the overall scores for all records
nominated and get a top 30 for the whole site.

This obviously takes some time (especially when factoring in the actual
web design terms, such as formatting the lists properly and linking
them to our reviews of the records), so this year I decided to use a
combo of mysql and php to handle it. Here's my process:

Create a "nominations" table containing the record/band name and an ID
number as a foreign key linking it to the 'id' column of our "reviews"
table. I would just use the reviews table for this but there are some
records that we didn't review that people want on their list.

Create a 'vote.php' page that presents 30 dropdown boxes populated by
the contents of "nominations". Staff select their records and submit
the results:

A second table, "votes", contains 31 columns (a column for 'staff_id'
and a column for each rank of the vote). A sample row from that table:

author id | 1 | 2 | 3 etc
16 | 444 | 555 | 666

Now, my problem is exactly how to total up the scores from this
database schema. I thought about just adding a 'score' column to
"nominations" and when the vote.php page is submitted, it just adds the
relevant number onto the score. However, I _know_ my staff and
inevitably, someone will want to change their ordering before it goes
live in a month, so I need to have the ability to dynamically
recalculate rather than have to mess around subtracting totals here and
there.

Also, if I record each writer's votes, I can work out some more
interesting stats beyond just "overall top 30" (stuff like "most/least
nominated record" etc), which is always good.

I've tried to figure out exactly how to extract the overall scores but
it's beyond me - can anyone give me advice? Basically, I need to look
up a record id (say 444), log all the 'positions' it reaches and then
add up all the points. Does this make sense, and is it feasible?

Matt