Hi Keith,
First, your question:
"Am I trying to normalize too much?"
In my opinion: yes, you are. I'll give you two reasons.
1: Based on your description, I think answers are (wrt their place in the data model) comparable to last names. Sure, there will be duplicates, but in most applications (genealogy excepted), this is not at all relevant. Almost all databases simply store the string "Jones" once for each person in their database named Jones. I would only consider a table of last names and a foreign key from the Persons to the LastNames table if I had to record attributes that functionally depend on the last name, or if I had to keep track of last names, even if nobody in my database currently uses that last name - which probably means that I'd only do it for a genealogy database (and maybe not even there).
2: Think about the usability aspect as well. With a table of common answers, how do you picture the user interface? You can give the user who sets up the poll free input, check if the answer has been used before, then either link to that answer or create a new one to link to - but in that case you'll get lots of "duplicates". Not truly duplicates (that's what the check is for), but different ways to express the same. E.g. "1 - 10", "1 to 10", "From 1 to 10 inclusive", "Less than eleven", etc. are all different ways to express the same range. You could avoid that by presenting all answers already used before in a dropdown, with "Add new answer" as an extra option - but once the application starts getting big, the dropdown will gro to thousands of answers, and how many users will actuallly read them all to choose the right one, instead of quickly adding a new option?
Another comment:
You mention an "IsCorrect" column. This surprises me. You write that the database has to handle polls, surveys. There are no "correct" or "incorrect" answers in polls; they are used to get a feel for how the population at large (or a selected group) feels about issues where there is no correct answer. To the question "Would you become a vegetarian if meat were 40% more expensive?", no answer can be defined as correct or incorrect; it's a personal choice for everyone. I would expect an "IsCorrect" attribute in a database for examinations and other similar tests. "12 - 5 = (a) 6, (b) 7, (c) 8, (d) 9" has only one correct answer, regardless of personal opinion, and if you pick the wrong answer you miss a point for your test.
Finally:
You wrote: "Another solution is to make an Answers table and a Responses table. Each response record would link 1 answer record (if the question required you to select an answer - otherwise just freeform text) to 1 question record and 1 user record."
That would be required in your idea with a generic reusable "Answers" table that every question then links to. Like I already said, that would be overly normalized.
I suggest something like this for the tables "lowest" in the hierarchy:
-- Table used for all questions in a test.
-- Questions should be numbered consecutively.
CREATE TABLE Questions
(TestID SomeDatatype NOT NULL,
QuestionNr smallint NOT NULL,
QuestionText varchar(200) NOT NULL,
-- maybe some other columns as well,
PRIMARY KEY (TestID, QuestionNr),
FOREIGN KEY (TestID)
REFERENCES Tests(TestID),
CHECK (QuestionNr) > 0
);
-- Table used for the answer options of multiple choice questions.
-- Options should be numbered consecutively.
CREATE TABLE AnswerOptions
(TestID SomeDatatype NOT NULL,
QuestionNr smallint NOT NULL,
OptionNr tinyint NOT NULL,
OptionText varchar(200) NOT NULL,
-- maybe some other columns as well,
PRIMARY KEY (TestID, QuestionNr, OptionNr),
FOREIGN KEY (TestID, QuestionNr)
REFERENCES Questions (TestID, QuestionNr),
CHECK (OptionNr) > 0
);
-- Table used to hold respondent's replies.
CREATE TABLE Replies
(RespondentID SomeDatatype NOT NULL,
TestID SomeDatatype NOT NULL,
QuestionNr smallint NOT NULL,
OptionNr tinyint NULL,
ReplyText varchar(200) NULL,
-- maybe some other columns as well,
PRIMARY KEY (RespondentID, TestID, QuestionNr),
FOREIGN KEY (RespondentID)
REFERENCES Respondents(RespondentID),
FOREIGN KEY (TestID, QuestionNr)
REFERENCES Questions (TestID, QuestionNr)
FOREIGN KEY (TestID, QuestionNr, OptionNr)
REFERENCES AnswerOptions (TestID, QuestionNr, OptionNr),
-- Must provide multiple choice option or freeform reply text!
CHECK (OptionNr IS NOT NULL OR ReplyText IS NOT NULL),
-- Do not provide multiple choice option AND freeform reply text;
-- remove this constraint if you want to allow multiple choice + extra text.
CHECK (OptionNr IS NULL OR ReplyText IS NULL)
);
The most important limitation in this design is that it allows a user to provide freeform text responses to a multiple choice question. There are no straightforward ways to prevent that using constraints. There are some "not-so-straightforward" ways. Let me know if you think you need them - but I can imagine that your interface would not present the user with a freeform text input for a multiple choice question, in which case the need for such constraints might not be high, and not be worth the overhead.
--
Hugo Kornelis, SQL Server MVP
Database Design: Web Polls
>
