Thread: Design question

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    17
    Rep Power
    0

    Design question


    Hi all,
    Before I'm starting to build my next project I wanted your advice on two issues:

    A) I'm building a large survey (about 200-250 questions). I can do it in two ways:
    1. Make a unique page for each question
    2. Make one or two pages which will access the database in which the questions will be stored.

    What's your opinion about making it a more secure and faster website?

    B) Would you advice on making a large insert query at the end of the survey (that will include about 250 variables) or make several insert queries along the way? (update the row every 50 questions or so).

    Thanks,
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,122
    Rep Power
    9398
    A) #2. One page would be enough to handle everything, but you could separate it into two pages: one for showing questions, one for saving answers.
    B) I would do an INSERT for the first "page" and UPDATES for the remainder. Advantages include getting a row into the table sooner and thus an ID, making it easier to save progress through the survey, and keeping track of complete versus incomplete results (starts off incomplete, marked as complete after the final set of questions).


    How I would do it, given two minutes to think about it and not knowing the full story behind this project:

    I could easily see grouping some questions together into distinct "pages" - for a short survey probably not but if you have >200 then probably so. So that's a new entity in the system: a "page of questions".
    Code:
    page_id | page_title
    --------+---------------------
          1 | Personal Information
          2 | Favorite Things
          3 | Multiple Choice
    There's also the questions themselves, of course...
    Code:
    question_id | page_id | question_text
    ------------+---------+--------------
              1 |       1 | Gender
              2 |       1 | Age
              3 |       2 | Favorite color
    ...and their possible answers.
    Code:
    answer_id | question_id | answer_text
    ----------+-------------+------------
            1 |           1 | Male
            2 |           1 | Female
            3 |           1 | Transgender
            4 |           1 | No answer
            5 |           2 | 1-18
            6 |           2 | 19-25
    Then the place to put the chosen answers.
    Code:
    result_user | question_id | answer_id
    ------------+-------------+----------
              1 |           1 |         4
              1 |           2 |         7
    (You'd also need a table for the users. This is for one survey per user: if you want multiple surveys then you'd need a table for users/results, and the table of chosen answers would use that as its foreign key.)

    And there's the hard part out of the way.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    78
    Rep Power
    0
    A) I'm building a large survey (about 200-250 questions). I can do it in two ways:
    you don't provide enough info, but more than 2 ways to do this...

    a large survey is not <500 questions. but please provide a few...

    let's do any survey:

    you need to store the questions, kinda sorta. need to store to keep track. create a question db tbl

    question table
    q_id is sequencer/auto-inc(uniquely ids ?)
    q_q is text AND the question, this is important.
    //ignore these
    q_type
    q_order
    q_cat
    q_cnt
    q_rel
    q_dup

    Then you need a survey table! Didya ever wonder why there are no OO DBs?

    Ok, you don't define this well, but a survey should define:

    the question(s) they include, perhaps order of the questions? and should be named(so we can keep track of the surveys?)

    survey table
    s_id is sequencer/auto-inc(uniquely ids ?)
    s_name(bingo)
    //ignore these
    s_type
    s_order
    s_cat
    s_cnt
    s_rel
    s_dup

    About all you need? right?

    Ok, let's go with your first survey? post back the questions, and we can populate the DB tbls and move on to the user interface(ui) and how people would actually be 'surveyed'.

IMN logo majestic logo threadwatch logo seochat tools logo