Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having trouble thinking this (simple) table relation through. I am setting up a quick SQL DB to populate labels and textboxes dynamically on screens. This is just to prove we can do it, so it doesn't have to be the final set up. We will not be storing any response data at this time, so a storage table is not necessary. The requirements are:

1) One question can have many possible answer options. ex: (True, False) (Yes, No)

2) One question can only have one group of answers. ex: Group One: (True, False) Group 2: (Yes, No)

3) Many questions can use one group of answers. ex: Drivers License? Group Two (Yes, No); Married? Group Two (Yes, No)

I'm drawing a blank on how to build a group of answers off of a set of answers, and allowing a question to only pull from that group of answers. Any help would be greatly appreciated :)

Thank you in advance.

share|improve this question
add comment

1 Answer 1

Here's one idea:

CREATE TABLE dbo.AnswerGroups
(
  AnswerGroupID INT IDENTITY(1,1) PRIMARY KEY,
  Description NVARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.Answers
(
  AnswerID INT IDENTITY(1,1) PRIMARY KEY,
  AnswerGroupID INT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Questions
(
  QuestionID INT IDENTITY(1,1) PRIMARY KEY,
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.AnswerGroupQuestions
(
  AnswerGroupID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  QuestionID INT NOT NULL UNIQUE FOREIGN KEY
    REFERENCES dbo.Questions(QuestionID),
  PRIMARY KEY(AnswerGroupID, QuestionID)
);

That makes all of this legal:

INSERT dbo.AnswerGroups(Description) SELECT 'YesNo';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(1,'Yes'),(1,'No');

INSERT dbo.AnswerGroups(Description) SELECT 'TrueFalse';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(2,'True'),(2,'False');

INSERT dbo.Questions(DisplayText) SELECT 'Driver''s License?';

INSERT dbo.AnswerGroupQuestions(AnswerGroupID, QuestionID)
  VALUES(1, 1);

INSERT dbo.Questions(DisplayText) SELECT 'Married?';

INSERT dbo.AnswerGroupQuestions(AnswerGroupID, QuestionID)
  VALUES(1, 2);

But prevents this assigning, say, one question to more than one answer group:

INSERT dbo.AnswerGroupQuestions VALUES(2, 1);

Result:

Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'UQ_AnswerGr_0DC06F8DA7923A33'. Cannot insert duplicate key in object 'dbo.AnswerGroupQuestions'. The duplicate key value is (1). The statement has been terminated.


A better design, as @DForck42 correctly observed:

CREATE TABLE dbo.AnswerGroups
(
  AnswerGroupID INT IDENTITY(1,1) PRIMARY KEY,
  Description NVARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.Answers
(
  AnswerID INT IDENTITY(1,1) PRIMARY KEY,
  AnswerGroupID INT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Questions
(
  QuestionID INT IDENTITY(1,1) PRIMARY KEY,
  DisplayText NVARCHAR(255) NOT NULL,
  AnswerGroupID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerID)
);

Only slightly different sample inserts:

INSERT dbo.AnswerGroups(Description) SELECT 'YesNo';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(1,'Yes'),(1,'No');

INSERT dbo.AnswerGroups(Description) SELECT 'TrueFalse';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(2,'True'),(2,'False');

INSERT dbo.Questions(DisplayText, AnswerGroupID) 
  SELECT 'Driver''s License?', 1
  UNION ALL SELECT 'Married?', 1;

And of course since there is only one possible value to link a Question to an AnswerGroup, there is no need to demonstrate the prevention of multiple Question -> AnswerGroup associations.

share|improve this answer
    
Thank you, just what I needed to get me going. Much appreciated. –  Bill Jan 20 '13 at 0:57
    
@Bill if your question was answered then accept the answer. –  Mr. Radical Jan 20 '13 at 4:20
1  
I don't see the need for AnswerGroupQuestions when you could just put a non-nullable FK to AnswerGroups in the Questions table since one question can only have one Answer Group. –  DForck42 Jan 21 '13 at 21:44
    
@DForck42 yep, that's a good point, I missed that limitation on first pass and was lazy to go back and throw a unique on that column. –  Aaron Bertrand Jan 21 '13 at 21:46
    
S'alright. I was looking and trying to figure out what the needs was for, but I wasn't seeing it. –  DForck42 Jan 21 '13 at 21:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.