9

I have an Answers table and a Questions table.

The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what its intended answer value type should be.

It will be important to parse these Answer values at one point or another since the numbers, at least, will need to be compared.

For a little more context, the questions and potential answers (typically a data type allowed for a textbox type input) are supplied by some users in a survey of sorts. The answers are then supplied by other specified users.

A couple options I've considered are:

A. XML or string that gets parsed differently depending on the intended type (which is kept track of in the question)

B. Three separate tables that reference (or are referenced by) the Answer table and are joined to based on the intended type. In this case, I'm not sure of the best way to set up the constraints to ensure each question has only one answer, or if that should be left to the application.

C. Three separate columns on the Answer table that can be retrieved based on the intended type.

I'd be happy just to get some input on the pros and cons of these approaches, or alternate approaches I hadn't considered.

2

It really depends how your front-end accesses the data.

If you are using an O/R-mapper, focus on the object-oriented design of your classes, not on the database design. The database then just mirrors the class design. The exact db design depends on the O/R-mapper and inheritance mapping model you are using.

If you are accessing the tables directly through record sets, data-tables, data-readers or the like, a simple thing to do, is to convert the values to a string by using an invariant culture and to store it in a simple text column. And, of course, use the same culture again in order to convert the text back to the specialized value types when reading the values.

Alternatively you can use one column per value type. We have terabyte drives today!

An XML column is possible, but probably adds more complexity compared to the simple text column and does pretty much the same thing, namely serializing/deserializing.

Separated joined tables are the correct normalized way of doing things; however, they add quite some complexity as well.

Keep it simple.

See also my answer to Questionnaire database design - which way is better?.

4

Based on what you have said I would use the following general schema:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
)
CREATE TABLE [dbo].[PollOption]
(
    [PollOptionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollQuestionId] INT NOT NULL,  -- Link to the question here because options aren't shared across questions
    [OptionText] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL  -- Remove this if you don't need to hide options

    CONSTRAINT [FK_PollOption_PollQuestionId_to_PollQuestion_PollQuestionId] FOREIGN KEY ([PollQuestionId]) REFERENCES [dbo].[PollQuestion]([PollQuestionId])
)
CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

You don't really care if the answer is a number, date, word, etc. because the data is an answer to a question not something you need to operate on directly. Furthermore the data only has meaning in context to the question. As such an nvarchar is the most versatile human readable mechanism for storing the data.

The question and the potential answers would be gathered from the first user and inserted into the PollQuestion and PollOption tables. The second user who answers the questions would select from a list of answers (true/false = list of 2). You can also expand the PollQuestion table to include the creator's user id if appropriate in order to track the questions they create.

On your UI the answer the user selects can be tied to the PollOptionId value. Together with the PollQuestionId you can verify that the answer is valid for the question quickly. Their response if valid would be entered in the PollResponse table.

There are a couple potential problems depending on the details of your use case. If the first user wants to use a math question, and you don't want to offer multiple possible answers. Another situation is if the options the initial user provides aren't the only options the second user can choose. You could rework this schema as follows to support these additional use cases.

CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NULL,
    [PollQuestionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [AlternateResponse] NVARCHAR(50) NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

I would also probably add a check constraint to make sure that either an option is provided or an alternate response, but not both (option and alternate response), depending on your needs.

Edit: Communicating datatype for AlternateResponse.

In a perfect world we could use the concept of generics to handle various datatypes for the AlternateReponse. Alas we don't live in a perfect world. The best compromise I can think of is to specify what the AlternateResponse datatype should be in the PollQuestion table, and store the AlternateReponse in the database as an nvarchar. Below is the updated question schema, and the new datatype table:

CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [QuestionDataTypeId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
    -- Insert FK here for QuestionDataTypeId
)
CREATE TABLE [dbo].[QuestionDataType]
(
    [QuestionDataTypeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Description] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
)

You can list all available data types for question creators by selecting from this QuestionDataType table. Your UI can reference the QuestionDataTypeId to select the proper format for the alternate response field. You aren't limited to TSQL data types, so "Phone Number" can be a data type and you will get appropriate formatting/masking on the UI. Also if required you can cast your data to the appropriate types via a simple case statement in order to do any kind of processing (select, validation, etc.) on the alternate answers.

0

Have a look at What is so bad about EAV, anyway? by Aaron Bertrand for some information on the EAV model.

It'll likely be better in multiple ways to have a column for each data type instead of having XML or multiple tables.

The constraint part is easy:

CHECK 
(
    CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + 
    CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + 
    CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END = 1
)

There are many existing questions and answers on this site tagged , and probably others where the asker didn't know to use that term in their question.

I highly recommend reading through those, as they will likely cover all of the pros and cons (this prevents people from re-hashing them here, when in reality they haven't changed).

Answer based on question comments left by Aaron Bertrand

-1

I think the problem is given too much thought or there are some additional constraints in why certain answers might be more accaptable then others. Currently there seems to be no evidence that the Answer would have to be processed in any way by the DB but just as a log field.

I would go with an NVARCHAR(MAX) and then just let the frontend deal with storing/retrieving the content. Possibly a IS_CORRECT bit field where the frontend could store if the Answer is correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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