MySQL/PHP Logic "Voting" System

I'm trying to build a system that will take a record from our database, allow a user to "vote" on it (text field, not preset options) and have the system decide when 3-5 of the same responses have been received, make that the 'final answer' and move on.

For Example, let's say we have a record called "Bob". We'll ask users what city Bob is from....there will be no preset options, so the user will have to answer in text form. I want the system to understand that there may be junk entries. So if somebody says 'Boston', somebody else says 'New York' and three others say 'Atlanta', I want the system to understand that Atlanta is the answer we're going to go with and set it as the final answer.

That's not exactly what we're doing, but it's a simplified version for purpos开发者_开发技巧es of explanation.

What I need help with is figuring out the best way to code something like this using MySQL and PHP. How should we store this in the DB, how do I deal with the bogus answers, track how many times one answer is given (Atlanta in the above example), etc.

A basic and inefficient way of doing this would be to have a column for each answer and add one to the number in the column when it's chosen....when that number reaches 3, we trigger the end process.

Thanks in advance for the help! Alex

---

My instinct is to say:

• A column for what you're voting on (perhaps an ID that references the question "Where's Bob from?" in another table.
• A column for the user's answer (canonicalized to some extent).
• A column for tracking info (IP address, user ID if available, etc).

This will let you detect and remove spam entries individually, and also programmatically deny re-voting on the same question from the same user and/or IP.

Then to determine when a question has its "final answer," you can just count up the votes (each time an answer is submitted), and see if you've reached your 3-5 vote threshold. For efficiency, I suppose you only need to check the answer that was submitted, as other responses won't just spuriously gain votes.

---

There is no easy solution for this. Because you want to allow for free text inputs rather than static choices. Any time you give someone the option of free text you should expect to get different results. Even if two users mean to enter the same thing they may enter it differently. A simple solution would be two people entering San Francisco. One could enter "San Fran" while another enters "SanFrancisco." And there could be many different combination. Especially when you account for people misspelling or using slang.

I would highly recommend avoiding free text for your situation. But if you have to, you could make a database of answers, similar to your suggestion. Instead of the plain-text answers I would suggest using a different approach. Maybe the MySQL Soundex Function. Which will return a string representing the sound of what the user entered. This is assuming you are having users enter single words. You could further expand it by comparing the levenshtein distance of soundexes (to find similar-sounding words).

Using a soundex may help match things slightly better, but it is still prone to problems. There are a lot of different things you could employ, like approximate string matching, regular expressions and PHP's similar_text function. These could all improve your results, but will all most likely add complexity.

But as far as I can tell there is no easy solution.