Mark Hensler — Thu, 01 Feb 2001 06:14:03 +0000

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.

Gil — Wed, 31 Jan 2001 21:14:44 +0000

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...

Peter J. Boettcher — Wed, 31 Jan 2001 19:53:13 +0000

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.

Mark Hensler — Wed, 31 Jan 2001 19:11:53 +0000

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.

Peter J. Boettcher — Wed, 31 Jan 2001 14:15:53 +0000

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.

Mark Hensler — Wed, 31 Jan 2001 02:54:03 +0000

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

Gil — Wed, 31 Jan 2001 01:24:22 +0000

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?

Mark Hensler — Wed, 31 Jan 2001 00:15:37 +0000

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?

KLWong — Tue, 30 Jan 2001 20:44:27 +0000

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 J. Boettcher — Tue, 30 Jan 2001 17:30:51 +0000

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.