guitarromantic@gmail.com wrote:
> 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

No, and no.

I think I'd probably structure the database something like this
(simplified obviously):

voters(voter_id*,firstname)
1 'Guy'
2 'Gwyneth'

records(record_id*,title)
1 'Like a Virgin'
2 'Hung Up'
3 'Clocks'
4 'Yellow'

votes(voter_id*,record_id*,points)
1, 1, 3
1, 2, 2
1, 3, 1
2, 3, 3
2, 4, 2
2, 1, 1

* = Primary Key

>From this it should be easy to see which songs Guy and Gwyneth like
best, which song is liked best overall (in this case a tie between
'Like a Virgin' and 'Clocks') and which song is nominated least often
(a tie between 'Hung up' & 'Yellow').