Design Problem - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Design Problem

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2007-07-17 : 12:22:48

I'm putting together a survey application and need some quidance on the SQL side of things. The issue is that each survey has multiple questions, but each question is of a different type, and requires different details to be stored.


tblSurveys

	SurveyID(PK)	Name	Description
	1		People	Survey about people
	2		Places	Survey about places
	3		Things	Survey about things

tblSurveyQuestions

	QuestionID(PK)	SurveyID(FK)	Text			QType
	26		1		Who's your daddy?	OpenText
	27		1		Rate your experience.	Likert
	31		2		Where do you live?	MultChoice
	32		2		Are you happy?		Likert

Problem: Each question has additional details that apply specifically to a question type. For example, question 31 is a Multiple Choice question, and it will require answers from which to choose. Question 32 is a Likert (rating) question, so the additional details will be a choice of numeric rating vs. custom text rating (satisfied vs. dissatisfied). Question 26 is OpenText, so it may require a character limit.

I'll probably end up with a table for each question type (see below), but what is the best way to connect the Survey to all of its questions (regardless of type), then each question to its appropriate details?


tblLikertDetails

	ID	RatingType(PK)
	101	Numeric_Rating
	102	Agree_vs_Disagree

tblMultChoiceDetailGroupings

	ID
	50
	51
	52

tblMultChoiceDetails

	ID(PK)	Group(FK)	Text
	20	50	< $100
	21	50	> $100
	22	51	Breakfast
	23	51	Lunch
	24	51	Supper
	25	52	Omaha
	26	52	Phoenix
	27	52	Dallas

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-07-17 : 15:05:54

Would another table help ? -

tblQuestionDetails

QuestionID LikertID MultID ...
31 NULL 52
32 102 NULL

You've to join necessary tables based on the Question type

MrRay
Starting Member

28 Posts

Posted - 2007-07-19 : 10:02:42

Good suggestion. Is there a benefit to breaking it out in additional tables, or should I simplify it by putting the question IDs right in tblSurveyQuestions? Like so:


tblSurveyQuestions

QuestionID(PK)	SurveyID(FK)	Text			QType		LikertID	MultID	TextID, etc...
26		1		Who's your daddy?	OpenText	Null		Null	123
27		1		Rate your experience.	Likert		151		Null	Null
31		2		Where do you live?	MultChoice	Null		112	Null
32		2		Are you happy?		Likert		141		Null	Null

Thanks

quote:
Originally posted by cvraghu

Would another table help ? -

tblQuestionDetails

QuestionID LikertID MultID ...
31 NULL 52
32 102 NULL

You've to join necessary tables based on the Question type

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-07-19 : 12:47:55

I thought a single question might have multiple entries in this table, especially for Multiple choice answers. But there is a grouping id. So i think it should be fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources