Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bogey
Posting Yak Master
166 Posts |
 Posted - 2004-07-08 : 16:13:23
|
We are building a questionaire with around 50 or so questions that are either A,B,C, OR D. How would you go about setting up the tables col structure? Remember that a question may have more than one answer.thanks. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-08 : 16:38:03
|
| You have a table of Questions. Each Question has Related AnswersThere's a 1 to many relationship between Answer.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2004-07-08 : 16:45:09
|
| I should of said Survey and not Questionaire. So there are no "correct" answers just Survey Questions and Feedback (a, b, c, or d) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-08 : 16:59:14
|
| Ok Then you have thisSurvey has many Questions which have many AnswersThen a User has a UserSurvey (an instance of a Survey if you will) that is a many to man between User and Survey. To store what the user selected, you need a UserAnswers table that is a Many to Many join between User and Answer. The UserAnswer Table has the UserID, AnswerID, and answer value (the text they entered or the letter they selected).If you have any other questions, let me know. I've done this sort of thing a few times.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-08 : 18:37:37
|
| As Michael said; when I've done this before we stored in the Question what type of data the answer could be (multiple choice [radio button / checkbox / dropdown list], free text, and so on); for multiple choice the Answers table would contain the appropriate choices. Then the application could dynamically display the right type of input fields.Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-09 : 12:05:59
|
| Yep, that's pretty much how mine works. Each Question has a QuestionType that tells me how to render the answers assoicated to that question.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2004-07-12 : 10:37:26
|
| MichaelP - What if we are not tracking the users. Your using a userId. We anticipate that the user will fillout "1" survey. Correct me if I'm wrong but should my table layout look like the.Table 1: QuestionsPK QuestionId Question TypeTable 2: AnswersPK AnswerIdFk QuestionId AnswerTable 3: UsersAnswersFK AnswerId**If we track users put a user Id in here or use a sessionID** sessionIdThanks |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-12 : 12:11:10
|
| Yep, that sounds about right. Don't forget that you need a AnswerValue in Table 3. If you are doing completly multiple choice, you don't need it, but if you are allowing for text answers, you need to store that text that the user enters in table 3.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Your input on col names for this type of table