Page 1 of 1

Multiple logged in user voting system theory

Posted: Tue Jan 20, 2004 6:03 am
by robster
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

Posted: Tue Jan 20, 2004 7:06 am
by timvw
2400 rows is peanuts...

Posted: Tue Jan 20, 2004 7:07 am
by robster
timvw wrote:2400 rows is peanuts...
add another 0. 24000. Is that still easy, I really don't know :)

ta :)

oh, and what do you think about the logic btw?

Posted: Tue Jan 20, 2004 7:29 am
by timvw
24000 is still peanuts (Don't worry about performance problems untill you really meet them. Only worry about your database design etc (as in: is it flexible enough? Are the dat normalized?)

About your code: The way you put it, is that you'll end up with one big form that submits values for every single movie on that page. Why? Wouldn't it be better to have a form for each single movie?

I think votes would be a better name for your voting table.
Why you want to save the month and year as separate entities is unclear for me. Just use a mysql timestamp for it. If you want to get all entries for a given year/month just use the GROUP BY clause in your query.