What's an effecient way to store a questionnaire in a database?

Since questionnaires can always change and the questions themselves could be lengthy it seems silly to use questions as column names. Is there any convention or proven method for storing a questionnaire within a database?

I was thinking of having a table with (Question-ID, Question) and then a second table for question-id and answer. But this solution might be too slow cause a t开发者_StackOverflow中文版hird join would be necessary to join the questions with a particular user.

What's wrong with a join? That's the entire point of a relational database, a join.

Store questions in one table.

Store question answers in another table.

If answers are predefined those and are common among more then one question then store common answers in their own table and create another table that has a QuestionID with an AnswerID.

Don't be scared of joins, they are part of relational databases. Without joins you're merely dealing with flat files.

I've late a bit with my answer, but maybe someone will find this information useful.

I'm fully agreed with JonH. I'll just give an example of how I've implemented it to make common solution more clearly.

The most common tables are:

1. Questions: Id (PK), Question, Description, OrderNumber, SectionId (FK to QuestionsSections.Id), AnswerTypeId (FK to AnswerType.Id)
2. Answers: Id (PK), Answer, SubmitDate, UserId (FK to Users.Id), QuestionId (FK to Questions.Id)
3. Questionnaire: Id (PK), Name, Description
4. QuestionnaireQuestions: QuestionnaireId (PK, FK to Questionnaire.Id), QuestionId (PK, FK to Questions.Id)
5. QuestionsSections: Id (PK), Name, Description, OrderNumber
6. AnswerType: Id (PK), Type, Description

Additionally you can prefer to create special table for optioned answers (e.g. radios, checkboxes, etc.), or activity tracking table, depending on your application needs.

Below are links which can be helpful (suggested sequence for reading from up to down):

• Questionnaire database design - which way is better?
• Database Design: Web Polls
• How to keep long multiple of questions and answers in database?
• Designing a database to hold a number of questionnaire definitions

I think the best possible answer is to model the information that the questionnaire is about. Don't try to model the questionnaire itself - the questionnaire is just the means used to gather the database content.

I think it would be best to have a separate class/file representing the questionaire using a hashmap. And then other classes can utilize it. I don't really see the need of storing the questions in the db since they would still be loaded into a hashmap.