CODEWITHSUNDEEP
mysqldatabasevb.netdatabase-designrelational-database
patrick ilboudo
patrick ilboudo

Reputation: 1

Database Design Advice - Multiple yes or no Questions

I ham currently designing a database table and the vb form that goes with it. The form will ask about 40 predefined yes/no questions to every user. How can I design the database table so that it store appropriately the answers. I would rather avoid having 40 or so attributes on the table. Is there a way to design it so that when you query the answers for a specific user you see the question and right next to it his answer(yes/no) Thank you

Upvotes: 0

Views: 950

Answers (3)

Pakk
Pakk

Reputation: 1339

Ok in simplest terms make a ("User")Table starting off with UserID as field 1. Make a Q table with all questions in them ( only if you don't want to hardcode these in your program) Make an Answers Table, this table should have 2 Columns... UserID, and Answer

you don't necessairly need to foreign key these 2 although its nicer in the long run when it comes to removing data ( it will let you know why you can't delete a user , in example if he still has qeustions that havent been delted , this is to avoid space that you really don't need to keep since the user will be deleted)

Then all you do is when you go to submit the questions 1 at a time or all at once, feed the UserID into the Answers Table, with the answer. so user 1 is always 1 and the answer will change.

If i was you, i would create the questions table aswell. So the whole thing would look like

User Table

UserID, UserName, UserPass, User..., User... , and so on

Question Table

QuestionID, Question

Answer Table

UserID,QuestionID,Answer

So in your program since you know what question corresponds to which questionID, you would insert

1(UserID),1(to40forQuestionID),answer.text(Answer) into answers table

then you would always be able to grab all your info from the answers table, knowing what answer belongs to wich question and to wich user and so on.

If you need terminology to go along witht his explaination hit me up, i tried to leave all of the Terminology out for simplification incase you didn't know them.

Upvotes: 1

Scott Allen
Scott Allen

Reputation: 533

This is how I would do it. List all the questions in the QUESTION table. This also means you can add new questions whenever you want just by adding new rows.

USER

UserId | UserName | Email | Ect..

QUESTION

QuestionId | Question

USER_RESPONSE

UserId | QuestonId | Response

SELECT
user.Name,
question.Qustion,
user_response.Responce
FROM
user
INNER JOIN user_response
ON user.UserId = user_response.UserId
INNER JOIN question
ON user_response.QuestionId = question.QuestionId
WHERE
user.UserName = 'JoeBloggs'

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

You can store the questions and answers in a separate table. Specifically you'll have a Questions table that has the 40 yes/no questions. The Answers table will have the columns for the question identifier, the user identifier, and the answer (for yes/no questions you could just use a boolean). AnswerChoices could also be a separate table.

There is a lot of possible flexibility depending on your needs. If the same user can take the survey more than once, you can have a user-surveys table and link answers to that instead. You can also have a Surveys-Questions mapping table in case multiple surveys are needed.

Upvotes: 0

Related Questions