5 Replies - 2349 Views - Last Post: 10 June 2012 - 03:42 AM Rate Topic: -----

#1 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

How to store survey results in database

Posted 09 June 2012 - 02:40 PM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: How to store survey results in database

#2 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: How to store survey results in database

Posted 09 June 2012 - 04:06 PM

Could have a table that takes a user ID and a comma separated list of title IDs for the movies/TV shows seen. The code would pull down just two values, and split and parse the comma separated list to get information regarding the titles viewed. It would save a lot of space and time compared to making new tables for every user, and follow a more maintainable and scalable design overall.
Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: How to store survey results in database

Posted 09 June 2012 - 04:06 PM

The method you are suggesting is definitely not the way to go. It should be possible to do it all with a normalised schema, and leave the heavy lifting to SQL. Take the following schema for example:
Surveys
    ID (PK int)
    Description (varchar)

SurveyOptions
    ID (PK int)
    SurveyID (FK -> Surveys.UD)
    Description (varchar)

Users
    ID (PK)
    Username
    Password
    etc,

SurveyResults
    UserID (FK -> Users.ID)
    SurveyOptionsID (FK -> SurveyOptions.ID)


That meets 5NF, and can easily be accessed by SQL to generate results of a survey. Personlly, I would prefer to break 5NF and go for 4NF by adding a column SurveyID (FK -> Surveys.ID) to the last table. This would ease query-string writing, as well as allow the application of a unique key on UserID-SurveyID, ensuring that a user can't store multiple choices. The downside is that the code would have to be carefully written with transactions and lookup checks to ensure DB data consistency.
Was This Post Helpful? 2
  • +
  • -

#4 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 542
  • View blog
  • Posts: 1,713
  • Joined: 08-November 08

Re: How to store survey results in database

Posted 09 June 2012 - 04:28 PM

Gonna move this to the Databases forum, since this is unrelated to PHP in specific.
Was This Post Helpful? 0
  • +
  • -

#5 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Re: How to store survey results in database

Posted 09 June 2012 - 09:58 PM

Thanks e__i__pi. Can you please explain in a bit more detail your last paragraph, with all that 4 NF and 5 NF stuff and query writing?
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: How to store survey results in database

Posted 10 June 2012 - 03:42 AM

4NF and 5NF are different normalisation forms. Usually, 3NF is enough, but in this case achieving 4 or 5NF is trivial. Any database schema you create should be at least 3NF otherwise you'll run into problems down the track. Wikipedia has a good set of articles explaining 1NF all the way up to 5NF. Here's the link to 1NF. If you scroll to the bottom, theres a link to 2NF, and 2NF has a link to 3NF and so on. They are well worth the read, though can be hard to digest.

In terms of query writing, give me some examples of the data you would need, and I'll throw some query strings back at you that would achieve the result.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1