Proper way to store a value that could be multiple different types

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.

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

StackExchange Database Administrators Q#109293, answer score: 4