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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2009-04-28 : 10:03:29
|
| I need to create a survey table in which will store 20 yes/no question.I created a 20 columns named them as c1, c2, …c20 to store 1/0 in each column for yes/no.But , where to store c1_question, … c20_question?For example, c1_question: Do you like this application? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-28 : 10:06:22
|
| What is c1, c2, c3....etc? Why not just have question1, question2, question3....etc?Store it as a INT value (0-no,1-yes, 2-maybe) then in your application layer, use an enumerated data type to translate that value. You can also define a user defined data type at the database level, but I'd just use an INT.EDIT:There is no reason to normalize this model if it's based on a "fixed-set" of questions. Normalization is a must if you're constantly adding/removing questions. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-28 : 11:04:48
|
quote:
There is no reason to normalize this model if it's based on a "fixed-set" of questions
Sure there are:1. They never remain "fixed sets" of questions2. It's a bad habit best avoided by not starting in the first place (ounce of prevention is a pound of cure)3. You'd still end up with separate tables for each survey, which is itself a bad design decisionHere's a simple schema to avoid the problem:CREATE TABLE Surveys(SurveyID int NOT NULL PRIMARY KEY, SurveyName varchar(50) NOT NULL)CREATE TABLE SurveyQuestions(SurveyID int NOT NULL REFERENCES Survey(SurveyID),QuestionID int NOT NULL, Question varchar(200), CONSTRAINT PK_SurveyQuestions PRIMARY KEY(SurveyID, QuestionID))CREATE TABLE SurveyAnswers(SurveyID int NOT NULL REFERENCES Surveys(SurveyID),QuestionID int NOT NULL, SurveyTaker varchar(50) NOT NULL, -- just a name, can be done with SurveyTakers table if preferredAnswer bit NOT NULL,CONSTRAINT FK_SurveyAnswers_SurveyQuestions FOREIGN KEY(SurveyID,QuestionID) REFERENCES SurveyQuestions(SurveyID, QuestionID)) Identities and other keys I'll leave to you, depending on how many surveys you have. |
|
|
|
|
|
|
|
|
How to design a survey table to store 20 yes/no ?