Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to design a database to hold the questions and possible answers for each questionnaire, but I end up having separate tables for each question. Is this correct or am I going wrong somewhere?

For example

Table for question x

  Answer  | Answer ID
       1         019
       2         089

I cant have a fixed size table for all questions as I don't have a maximum amount of answers. This obviously means I could end up with hundreds of tables, one for each question.

share|improve this question
    
You may find some ideas from similar question, dba.stackexchange.com/questions/9334/… –  a1ex07 Sep 4 '12 at 17:04
add comment

1 Answer 1

up vote 3 down vote accepted

We have an application at our workplace that does a similar thing. It works by having a table that contains a list of all possible questions like such:

CREATE TABLE QUESTIONS
(
   ID INT NOT NULL PRIMARY KEY,
   SUMMARY NVARCHAR(64) NOT NULL UNIQUE,
   DESCRIPTION NVARCHAR(255) NULL
);

Then you have an ANSWERS and a QUESTIONAIRES table defined using the same structure above. Once you have these two tables you then define a table to hold the list of question/answer possibilties as such:

CREATE TABLE QUESTION_ANSWERS
(
   ID INT NOT NULL PRIMARY KEY,
   QUESTION INT NOT NULL REFERENCES QUESTIONS(ID),
   ANSWER INT NOT NULL REFERENCES ANSWERS(ID)
);

Once you have these you can then create a table to contain the responses as such:

CREATE TABLE RESPONSES
(
   QUESTIONAIRE INT NOT NULL REFERENCES QUESTIONAIRES(ID),
   RESPONSE INT NOT NULL REFERENCES QUESTION_ANSWERS(ID)
);

This will give you maximum flexibility allowing you to add new questions and answers without having to change your database design frequently. It can get a bit complicated if you need to version the questions/answers but this should give you a good foothold to work from.

I hope this helps you.

share|improve this answer
    
Thanks for your response. Are you saying that the QUESTION_ANSWERS table will have multiple QUESTION(ID)s for different ANSWERS(id). I.e repetition of the same QUESTION(id)s, if one question had 5 possible answers you would have 5 instances of the QUESTION(id)? –  geminiCoder Sep 4 '12 at 15:18
    
Yes, that is correct. –  Mr.Brownstone Sep 4 '12 at 17:27
    
You probably need one more table to hold the list of responders (maybe with name, contact details, dates, etc), and then a extra field in the RESPONSES table to act as a foreign key to this table. This way you can get the responses per responder. –  adam f Sep 4 '12 at 22:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.