Database design

what kind of answers do you expect? true/false, multiple choice, or short answer?

There can be yes/no, short answer AND checkboxed/multiple choice answers.

This is a quicky design, but I might do something like this...

I think that you want to keep all the answers in one table to make it faster. For T/F questions, have two fields. For Multiple choice, have one field for each choice. For short answer, have one field.

The format is this:
QuestionID_ + t/f for T/F questions
QuestionID_ + choice (a,b...) for mulitple choice
QuestionID for short answer

Query the Questions DB for the Type of the question. Then build a query based on the type of question.

True/False, and mulitple choice questions will only occupy the first record of the answer table. This makes it easier and faster to acquire results. Short answer is a bit harder, but I want to keep them in the same table... So each answer for a short answer question will have to occupy a new record.

Again, this is a quicky design. I think it's a good one, but it may not the the best one.
Good Luck,

Gil,

I would recommend the following setup. I would use 3 tables, one for questions, one for answers, and one for user information.

UserTable:
User_ID integer identity (primekey)
UserInformation_One varchar(whatever)
etc, etc

QuestionTable:
Question_ID integer identity (primekey)
Question_One_Text varchar(whatever)
Question_Two_Text varchar(whatever)
etc, etc

AnswerTable:
Answer_ID integer identity (primekey)
Answer_ID varchar(whatever)
Question_ID integer (From QuestionTable)
User_ID integer (From UserTable)

So after someone submits this survey to get back their information all you would have to do is:

SELECT * FROM UserTable,QuestionTable,AnswerTable
WHERE User_Table.User_ID = Answer_Table.User_ID
AND QuestionTable.Question_ID = AnswerTable.Question_ID

Saving the answers as varchar might not be super efficient but it saves you trouble down the road if you ever have to change the survey.

My site (also a medical survey, although all answers are multiple-choice) uses Peter's method as well. I also left gaps between the values in the ID field -- which I always order the query by -- of the questions (e.g. using 10, 20, 30 ... rather than 1, 2, 3 ...) so that in the future if I need to insert a new question in between existing ones I can just pick an intermediate number (say, 25) for the new one.

Peter- That query will work with mySQL, but it's built wrong for SQL Server 7, and we don't know what is using yet.

Gil- what do you want to write this with?

This site will be utilizing MySQL. I'll be using PHP for processing the info. I believe Peter's solution will work, but can MySQL handle 100,000 answers in that answer table?

uh, well.... you'll want to use LIMIT
http://www.mysql.com/doc/S/E/SELECT.html

Mark/Max,

I'm curious as to why you would say that query is "built wrong" for SQL 7? I have almost no expereince with MySQL so my example was based on MSSQL which I've been using for almost 5 years, so if anything it's probably "built wrong" for MySQL.

Gil,

100,000 should be no problem. Remember, that's a pretty small table (memory wise) all it's holding is 3 integers and 1 varchar fields, that's nothing.

I'm not sure why you would want to use LIMIT. Your SELECT statement is going to be limiting the number of rows that are returned. If the user is going to be submitting the survey multiple times you might want to add a date field in the answer table. That would give you a differentiator as to which answers to retrieve for that user.

MSSQL = Microsoft SQL Server?
I've never used a query like that for multiple tables. I've always used INNER JOIN and such. Does is actually work that way?

I would have used the limit just so I could use a DO WHILE LOOP and not get 100k results printed to my screen. Also, the SQL server should stop looking after it reaches it's LIMIT, so the load on the server is shorter. (though perhaps not noticable )

For a mySQL server, SELECT limits the number of fields returned, LIMIT limits the number of records returned.

Mark,

You're right MSSQL is another way of saying Microsoft SQL Server. There's always multiple ways of performing queries, and yes this will work on SQL Server. I usually don't use JOINS in examples I do as this can sometimes lead to more questions

I'm still not sure why you would use LIMIT in this example though, maybe I'm missing something? The query will only return the amount of records corresponding to that user, not the whole 100,000. So let's say there are 300 questions, UserA fills out the whole survey and submits it, so now UserA has 300 records in the Answer table. The query that I originally posted would return 300 records which then could be looped through using a Do While...Loop.

If they submit it again then there will be more records which was why I suggested maybe adding a datetime field.

Thanks for all the support guys. I knew this database design would start a good post! The idea behind this database is that a patient will submit this survey within 6 months, a year and 2 years of a surgery. It is designed to keep track of the patient and generate statistics. So yes, I will need a date field. I was worried that having so many records would slow down the db, but Peter had a good point saying that there would only be like 4 fields, so there shouldn't be any problem...

I'm about to put this in my PHPMyAdmin and see how it goes...

I'm so sorry. I miss understood the question. I just re-read the original post. My initail reading made me think of an anonymous survey... not an ongoing thing where you would want to keep track of users. (I was thinking more along the lines of a poll than survey)

Sorry about that.