Sql-server – Setting up simple question & answer database

database-designsql serversql-server-2008

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.

Best Answer

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.

Related Question