HOW TO? Compile Votes from Top Songs

mredt

Sadly, numbers turn my brain into mush. I love coding, but my brain stops when I get into calculations. 🙂

Anyways, I am having people vote on their favorite 5 songs of 2006. The data is put into the following database fields:

database:votes
id, vote1, vote2, vote3, vote4, vote5, email

I also have a song database, with the following fields:
database::songs
id, artist, song

The song id (from the songs database) is put into vote1-5 (from the votes database)

So, when voting is over, how do I dynamically calculate the votes per song?? Where do I start to decide how to do this?

Thanks in advance!!

Medt

Piranha

The votes table should actually be 2 tables to avoid many columns with the same data. Normally you use a query like

SELECT songs.id, songs.artist, songs.song, COUNT(votes.id) 
FROM songs 
LEFT JOIN votes ON votes.vote = songs.id
GROUP BY songs.id

But with storing lots of votes in different columns in one table makes it much harder to calculate. And I'm not sure that it is the best way either, with a big database it will take some time to calculate this. It may be better to add a column in songs that you increment with one each time a user vote for that song, it would be much faster to get the result. Of course you can add the column, do the math and then add the result.

Edit: Search for "database normalisation" to get a better understanding about how to design databases.

mredt

you're right. I think I will add another column to the songs database (votes) and increment. Thanks so much!