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).
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".
There's also the questions themselves, of course...
page_id | page_title
1 | Personal Information
2 | Favorite Things
3 | Multiple Choice
...and their possible answers.
question_id | page_id | question_text
1 | 1 | Gender
2 | 1 | Age
3 | 2 | Favorite color
Then the place to put the chosen answers.
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
(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.)
result_user | question_id | answer_id
1 | 1 | 4
1 | 2 | 7
And there's the hard part out of the way.
July 10th, 2012, 03:52 AM
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
q_id is sequencer/auto-inc(uniquely ids ?)
q_q is text AND the question, this is important.
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?)
s_id is sequencer/auto-inc(uniquely ids ?)
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'.