How to store survey results in database

[Cupidvogel]

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?

[Barand]

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  | >--+

                          +----------+

 

[Cupidvogel]

Thanks. That looks simple enough, dunno why didn't it occur to me.

[Barand]

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

[Cupidvogel]

Right. Thanks.

[cpd]

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.