SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help/suggestions for a dB layout for survey wizard

    (mySQL)

    Hi guys,

    Still trying to figure out the best way to make a layout for my survey wizard project. The wizard isn't my biggest problem, but more of how to store the:

    questions
    choices for answers. if no choice it's a textbox or text area
    storing the responses
    storing vitals (name/phone/addy/site/etc)
    which local (building) the survey belongs to
    what type of question: dropdown/radio/check/text/area
    if it's a required question

    after getting those out of the way, i have to be able to compare among sites/zipcodes but i dont think that should be too hard after the layout is completed.

    i'm pretty new to the world of databasing and not too sure how to approach this. my initial idea was to have a seperate table for each survey.

    local#_question_1 (ex: 1111_question_1)
    local#_response_1 (ex: 1111_response_1)
    the format is in [#of building]_question/response_[survey#]

    question table would have
    - ID question choice type q_mand info info_mand

    type = type of question (radio/check/dropdown/etc)
    x_mand = if mandatory 1/0
    info = which vitals (name/phone/etc) to show

    response table would be just a field of rows so each question type would be in its own row. it might be a little much instead of adding everything together but this way we can mix/match how we want to compare various sites or see how ppl in one zipcode compare to those in another or a range of zipcodes.

    after reading kevin's book, my other thought was, because in the future, this could end up being 100s of tables (not too sure if that'll be good) to just make a bunch of tables with relavant info such as

    questions table (row = diff questions | col = diff surveys)
    type table -what type of question to show such as radio/check/etc (row = quest. | col = survey)
    q_required table (row = diff questions | col = diff surveys)
    info table -diff vitals to show (row = diff questions| col = diff surveys)
    q_required table (row = diff questions | col = diff surveys)

    but i run into another problem of how to store the choices if it's an enum or text/area

    any insight will be greatly appreciated

    just found a site similar to something i'll be making here

    My stuff
    --------
    Sample Survey (not 100%)
    questions db
    responce db
    (btw, i just made those questions/answers via phpadmin manually; didn't start wizard yet)
    Last edited by GeekSupport; Jun 23, 2002 at 11:26.

  2. #2
    SitePoint Member
    Join Date
    Jun 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Survey DB

    I design databases for a living. First decide what data really needs to be stored or persisted.

    A couple of questions would be... can anybody fill out one of these surveys or only 'members' or 'registered' people.

    If anybody can fill out a survey, then I would not store 'person' information if you do not have to. If only 'members' or 'registered' people or users can fill out a survey then you have to store person info, like last, first name, email, login id, password and really what ever your client or customer requires. HOWEVER, there are already some php forum software or skripts for this type of stuff.

    A couple of questions on the survey. Is it one survey or many? Can you make the survey or does the customer or client want to make it dynamically.

    The easiest way to make a survey (to get it up and running quickly) is tell the client or customer to limit the number of questions. Any more than a dozen and people get tired. Tell the client or customer that each question should be answered by a scale of 1-10, and maybe 11 would be N/A. Then finally a single question for comments or feedback. Also of importance is if the survey has to show the results real time.

    If you design a survey this way, and you don't need real time results.... you can actually get away with using radio buttons for the survey and one single textarea box. You can even write the results into a comma separated textfile. Then you can provide the textfile to the survey owners (customer or client) and they can import it into MS Excel very nicely.

    The textfile would look like this, if 2 people had answered the survey:

    q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,comments
    5,6,2,8,4,9,0,1,11,2,I thought your produc is great
    10,3,4,7,2,9,10,11,2,9,I did not like your product

    I have a sample survey on my site. The site is www.damacoc.org There is a Java Applet on the home page, and a text message that says 'Take our new web site survey' - click on that and it will take you to the survey.

    Simple, but effective.

    Alex
    akerezy@irisicom.com

  3. #3
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Survey DB

    Originally posted by Vmusic
    I design databases for a living. First decide what data really needs to be stored or persisted.

    A couple of questions would be... can anybody fill out one of these surveys or only 'members' or 'registered' people.

    Only moderators will have access to create new surveys
    A couple of questions on the survey. Is it one survey or many? Can you make the survey or does the customer or client want to make it dynamically.
    it will eventually end up being many surveys, hence the wizard tool after figuring out the best way to setup the tables/fields.
    The easiest way to make a survey (to get it up and running quickly) is tell the client or customer to limit the number of questions. Any more than a dozen and people get tired. Tell the client or customer that each question should be answered by a scale of 1-10, and maybe 11 would be N/A. Then finally a single question for comments or feedback. Also of importance is if the survey has to show the results real time.
    This would be a great idea; however, if our survey gets into politics, more text boxes at random areas are necessary. The wizzard will take care of this (when it prompts the user for the type of question such as text/area/check/radio/dropdown)
    If you design a survey this way, and you don't need real time results.... you can actually get away with using radio buttons for the survey and one single textarea box. You can even write the results into a comma separated textfile. Then you can provide the textfile to the survey owners (customer or client) and they can import it into MS Excel very nicely.
    AFAIK, only the mod will be able to see the results. i'll have to create another script as well as protected area just for mods
    The textfile would look like this, if 2 people had answered the survey:

    q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,comments
    5,6,2,8,4,9,0,1,11,2,I thought your produc is great
    10,3,4,7,2,9,10,11,2,9,I did not like your product

    I have a sample survey on my site. The site is www.damacoc.org There is a Java Applet on the home page, and a text message that says 'Take our new web site survey' - click on that and it will take you to the survey.

    Simple, but effective.

    Alex
    akerezy@irisicom.com
    I couldn't connect to the site, i'll check it out later. thanks
    Last edited by GeekSupport; Jun 22, 2002 at 14:09.

  4. #4
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think i have everything set if i make them all seperate tables.

    one table each for

    questions (varchar)
    choices (enum)
    mand_questions (int)
    local_num (varchar*5 or char*5)*
    vitals_ask (varchar)
    mand_vitals (int)
    title (varchar)
    date_start (date)
    date_end (date)
    active (int)**

    when browsing the table, col = diff survey, row = diff entry

    my only delema is still with the responces -- how to store them in the dB. it seems like every survey will have a different table for responces. that seems bad, but i can't think of any other solution right now.

    *should i do static or varchar? local_num is either 4 or 5 characters (numbers and letter extention)
    **active: 0 - Work in progress, 1 - Publish to web, 2 - completed survey, 3 - terminated survey
    Last edited by GeekSupport; Jun 23, 2002 at 01:10.

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks seanf, didnt even realize there was a dB forum gotta start reading up on these threads

    [edit1]
    did a google group search and found this post. any suggestion which one is better: Steve's or Jeff's or your own.
    Last edited by GeekSupport; Jun 23, 2002 at 11:42.

  7. #7
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how does this look for a dB structure?

    it is an excel workbook saved to html. the first tab would be the db as a flat file, 2nd tab isnt anything fancy, 3rd tab is the final outcome.

    the only problem i see is that the response table and choice table will become gigantic when a lot of surveys are created. i also need a better way to store fill-in questions.

    also, when displaying results, i would have to check...
    Code:
    if question_id's choice_id = null then 
         show the result from the text field 
    else
         show the responce's rel_choice_id's choice
    endif :p
    ...for every single response

    Below is how the relationships would look.


    the way it is now, it looks like i have to do two mySQL queries when displaying the questions to the user. one to get the title and another to get the list of questions displaying them with the proper "type" (radio/check/text/etc).
    Last edited by GeekSupport; Jun 26, 2002 at 19:20.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •