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.
Best Answer
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?.