CODEWITHSUNDEEP
sql-serverdatabase
Simone
Simone

Reputation: 1337

Managing both fixed and user-defined values

I'm designing the database for an application in which the user is presented with questions, and he must answer them. Think of it either as a questionnaire or as a quiz game, the concept applies to both. I plan to have:

Since many of the questions will be common cases, like yes/no, I decided I'd specify a "question type" enumeration to each question. If the application sees a yes/no question, for example, it means there are no answers in the database, and the application will automatically add the two answers, "Yes" and "No". This saves me hundreds or thousands of useless rows in the answers table.

However, I'm not sure how I should define the table to record user answers. Without the special types of questions, I'd just record the question ID, the answer ID and the user ID, which means "user X answered Y to question Z". However, "yes/no" questions would not have a matching answer in the table, so I can't use the answer ID.

Even making the answers shareable between questions (by making a many-to-many relationship between questions and answers) is not a good solution. Sure, it would allow me to define "Yes" and "No" as regular answers, but then applications should be aware that a "yes/no" question uses answers (say) 7 and 8 - or, when creating a "yes/no" question answers 7 and 8 should be bound to that question. But this means that these "special" answers' IDs must be hardcoded somewhere else. Also, this would not scale well should I add more special types of question in the future.

How should I proceed? Ideally, I need to store in each row of my "user answers" table either a fixed value or a foreign key to the answers table. Is there a better solution than using two columns, one of which is NULL?

I'm using SQL Server, if that matters.

Upvotes: 0

Views: 30

Answers (1)

kirchner
kirchner

Reputation: 974

Based on your description I think I'd go on the route of adding another column to the table and making the FK column nullable.

You'd probably have only a few choices for those special questions, so a nullable TINYINT datatype would cut it, and it is only 1 extra byte for your answer row. If this extra column happen to raise the number of columns to more than a multiple of eight, say you go from 8 to 9 or 16 to 17, than you pay another extra byte for the growth of the null bitmap. But it's 2 extra bytes per row worst case.

Upvotes: 1

Related Questions