Multiple logged in user voting system theory
Posted: Tue Jan 20, 2004 6:03 am
Hello 
I am about to code up a voting system for my site. (See http://10secondclub.net). I currently have all my users heading over to Yahoo! groups to do the voting there. Not nice and it's time to get rid of the Yahoo! groups page so I am going to have onsite voting.
Here's the way it will work in terms of how I want people to interact:
1- System checks if user is logged in
2- If they are, it displays the voting page
3- The voting page is a grid layout of thumbnails that link to their video files in a new window so users can watch the videos then vote on them.
4-Under EACH thumbnail is a rating line that lets them choose from (DO NOT VOTE - 1 - 2 - 3 - 4 - 5) So a user can choose not to vote for that particular animation or can give it a rating of 1 to 5. The default will be DO NOT VOTE.
5- After the user has put numbers next to all the entries he/she wants to vote for they click submit and the system will do the mojo behind the scenes.
OK, so now you know the system, here is some theory and I'd be really interested to hear other peoples opinions.
The Mojo Theory
Imagine I have a table called VOTING, it has these fields:
ID - UserID - EntryID - Rating - Month - Year
I also have a table called Entries where all the entries already reside, so each entry has these fields (amongst others):
ID - UserID - Rating - RatingAVG
So based on that...
1-Is user logged in?
2-If yes, then get their member id and stick it in $Uid
3-Parse thru the VOTING table where month is the same as the voting month AND year is the same as the voting year to see if the user has voted for this month.
4-If the user has NOT voted, then display the 1-5 rating box under each thumbnail
5-If the user HAS voted, then let them know they have voted under each thumbnail and what they voted (if they chose other than DO NOT VOTE. IE: 1, 2, 3, 4 or 5).
6-If user presses the vote button the voted for items get added to the database, the DO NOT VOTE items do not get added to database to save on space in database. These items are all added at ONE entry into the database per rating. So, if user 10 voted for 5 animations and gave them ratings it could look like this:
ID - UserID - EntryID - Rating - Month - Year
1 - 10 - 3 - 5 - 1 - 2004
2 - 10 - 7 - 3 - 1 - 2004
3 - 10 - 25 - 4 - 1 - 2004
4 - 10 - 31 - 5 - 1 - 2004
5 - 10 - 34 - 4 - 1 - 2004
At the end of the round, I use an admin panel to collate votes, and copy the ratings and create an average rating for each entry in the VOTING table that matches the month and year and copy them to the appropriate fields in the ENTRIES table. Another part of the code then displays this appropriately.
My big issue with this, putting ALL the entries for every month of every year into the one table is the size of it. Is there a size limit? I estimate based on historical data that around 100 people will vote each month on about 40 entries. Only the entries they gave a number to will get added to the database as we are not adding the DO NOT VOTE items. That is usually around 5 entries per person. That's around 500 entries into the table a month, around 6000 entries a year, and considering the site is 4 years old, in another 4 years (if i start the system this month) that comes to 24000 entries in one table.
The speed of searching through a table like this could be really REALLY bad for the server and for the end user. I don't think it's very scalable. Unless sytems are faster than I imagine.
Any ideas?

Thanks a ton for reading!
Rob
I am about to code up a voting system for my site. (See http://10secondclub.net). I currently have all my users heading over to Yahoo! groups to do the voting there. Not nice and it's time to get rid of the Yahoo! groups page so I am going to have onsite voting.
Here's the way it will work in terms of how I want people to interact:
1- System checks if user is logged in
2- If they are, it displays the voting page
3- The voting page is a grid layout of thumbnails that link to their video files in a new window so users can watch the videos then vote on them.
4-Under EACH thumbnail is a rating line that lets them choose from (DO NOT VOTE - 1 - 2 - 3 - 4 - 5) So a user can choose not to vote for that particular animation or can give it a rating of 1 to 5. The default will be DO NOT VOTE.
5- After the user has put numbers next to all the entries he/she wants to vote for they click submit and the system will do the mojo behind the scenes.
OK, so now you know the system, here is some theory and I'd be really interested to hear other peoples opinions.
The Mojo Theory
Imagine I have a table called VOTING, it has these fields:
ID - UserID - EntryID - Rating - Month - Year
I also have a table called Entries where all the entries already reside, so each entry has these fields (amongst others):
ID - UserID - Rating - RatingAVG
So based on that...
1-Is user logged in?
2-If yes, then get their member id and stick it in $Uid
3-Parse thru the VOTING table where month is the same as the voting month AND year is the same as the voting year to see if the user has voted for this month.
4-If the user has NOT voted, then display the 1-5 rating box under each thumbnail
5-If the user HAS voted, then let them know they have voted under each thumbnail and what they voted (if they chose other than DO NOT VOTE. IE: 1, 2, 3, 4 or 5).
6-If user presses the vote button the voted for items get added to the database, the DO NOT VOTE items do not get added to database to save on space in database. These items are all added at ONE entry into the database per rating. So, if user 10 voted for 5 animations and gave them ratings it could look like this:
ID - UserID - EntryID - Rating - Month - Year
1 - 10 - 3 - 5 - 1 - 2004
2 - 10 - 7 - 3 - 1 - 2004
3 - 10 - 25 - 4 - 1 - 2004
4 - 10 - 31 - 5 - 1 - 2004
5 - 10 - 34 - 4 - 1 - 2004
At the end of the round, I use an admin panel to collate votes, and copy the ratings and create an average rating for each entry in the VOTING table that matches the month and year and copy them to the appropriate fields in the ENTRIES table. Another part of the code then displays this appropriately.
My big issue with this, putting ALL the entries for every month of every year into the one table is the size of it. Is there a size limit? I estimate based on historical data that around 100 people will vote each month on about 40 entries. Only the entries they gave a number to will get added to the database as we are not adding the DO NOT VOTE items. That is usually around 5 entries per person. That's around 500 entries into the table a month, around 6000 entries a year, and considering the site is 4 years old, in another 4 years (if i start the system this month) that comes to 24000 entries in one table.
The speed of searching through a table like this could be really REALLY bad for the server and for the end user. I don't think it's very scalable. Unless sytems are faster than I imagine.
Any ideas?
Thanks a ton for reading!
Rob