I have two tables.
Table A: Contains a list of songs, song artwork, mp3 link, tags etc.
Table B: Contains registered user info, user id, username etc.
I am about to add a star rating system to the songs and would like to let any registered user vote only once per song.
So my plan initially was to create a third table and use a JOIN:
Table C: Containing songID, Total score(the sum of all votes cast), vote_count (number of votes) and perform a calculation clientside in jQuery to return the average vote.
I figured this would be optimal for performance since I will be dealing with extremely large datasets.
Of course using this method I would have no protection against users voting as many times as they want.
Therefore, my question is, what database set up would be best to protect against cheating (i.e. storing and checking against userID's of voters in table C) without degrading the performance of filtering/sorting the songs themselves as this is key to the project.
I hope I made this request clear, my apologies if not.
Make a vote table: ([userID, songID], rating)
Maybe put an index on songID for faster access.