Cupidvogel Posted June 9, 2012 Share Posted June 9, 2012 Hi, surely we all have seen in Facebook questions like Which of the following IMDB top 250 movies have you seen?, followed by a list of check-options with a button, which on clicking fetches all the members who have voted for that option (if there are two many number of such users, as for this particular question, where each option may be voted by more than a million users, the user-list is retrieved only partly and continues do so on subsequent requests from the client). Question is, how can the list of users be stored in the database? If the survey poll has an id of say, 123, the it is fair to assume that there will be a table like username_123, where there will be a column containing the option value, then one containing the option text. How to insert the list of users? One way may be by storing the user names in a string, delimited by space, and on requests, send the string to PHP, where PHP uses regular expression to split the string and extract the usernames. However the process looks very clumsy, and very painstaking, given the fact that the column value will have to be frequently updated as more voter vote, or some of the existing voters unvote (either by withdrawing their vote altogether, or voting another item in case of radio-buttons). Any idea, anyone? Quote Link to comment https://forums.phpfreaks.com/topic/263923-how-to-store-survey-results-in-database/ Share on other sites More sharing options...
Barand Posted June 9, 2012 Share Posted June 9, 2012 For each movie the user votes for you insert a "vote" record containing the user id and the movie id +-----------+ +------------+ | user | | movie | +-----------+ +------------+ | id |--+ +---| id | | name | | | | title | | etc | | | | etc | +-----------| | | +------------+ | +----------+ | | | vote | | | +----------+ | | | voteId | | +----< | userId | | | movieId | >--+ +----------+ Quote Link to comment https://forums.phpfreaks.com/topic/263923-how-to-store-survey-results-in-database/#findComment-1352538 Share on other sites More sharing options...
Cupidvogel Posted June 10, 2012 Author Share Posted June 10, 2012 Thanks. That looks simple enough, dunno why didn't it occur to me. Quote Link to comment https://forums.phpfreaks.com/topic/263923-how-to-store-survey-results-in-database/#findComment-1352572 Share on other sites More sharing options...
Barand Posted June 10, 2012 Share Posted June 10, 2012 It's the standard way to resolve a many-to-many relationship. eg customer order can have many products a product can be in many customer orders so have an orderItem table with orderId and productId Quote Link to comment https://forums.phpfreaks.com/topic/263923-how-to-store-survey-results-in-database/#findComment-1352587 Share on other sites More sharing options...
Cupidvogel Posted June 10, 2012 Author Share Posted June 10, 2012 Right. Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/263923-how-to-store-survey-results-in-database/#findComment-1352590 Share on other sites More sharing options...
cpd Posted June 10, 2012 Share Posted June 10, 2012 You could remove voteID and create a composite key from the remaining fields as it should probably be one vote per person, per movie. The composite key would be your primary key. Quote Link to comment https://forums.phpfreaks.com/topic/263923-how-to-store-survey-results-in-database/#findComment-1352594 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.