I've two tables.
Table A: Consists of a listing of tunes, song artwork, mp3 link, tags etc.
Table B: Consists of registered user info, user id, username etc.
I'm going to give a star rating system towards the tunes and want to let any registered user election only once per song.
So my plan initially was to produce a third table and employ a JOIN:
Table C: That contains songID, Total score(the sum of the all votes cast), election_count (quantity of votes) and execute a calculation clientside in jQuery to come back the typical election.
I believed this is optimal for performance since I'll be coping with very large datasets.
Obviously that way I'd don't have any protection against customers voting as numerous occasions because they want.
Therefore, my real question is, what database setup might be best to safeguard against cheating (i.e. storing and checking against userID's of voters in table C) without degrading the performance of blocking/sorting the tunes themselves because this is answer to the project.
I really hope I chose to make this request obvious, sorry otherwise.
Create a election table: ([userID, songID], rating)
Maybe put a catalog on songID for faster access.
Your third table ought to be structured like:
Song ID User ID Star Rating
- having a unique index on (Song ID, User ID). (You need to store Song ID and User ID together to have the ability to tell that has chosen - there's not a way for this.)
To come back a typical rating for any given Song, simply
select AVG(`Star Rating`) From `Rating Table` where `Song ID` = ?
With an indexed table, choosing a typical for any specific Song with under 1000 rankings should give reasonable access occasions.
- Album artist id, artwork
- Artist overall rating
- Song artist id, album, rating
That method for you to pull-up by artist, by song, by album etc.
is dependent how current you would like the rating to become, for star rating, it does not always need to right current. So that you can possess the
vote table: ([userID, songID], rating)
as Tom van der Woerdt recommended. however , you may also give a star rating to every song and recalculate it daily or every couple of hrs if you possess the capacity.