Store answers in sql table with unique id and store each answer in columns

Question

I've been programming for a year now but I never asked questions anywhere since now. I have a architecture issue. I have 78 questions and answers, the questions are static in the html code and all of them could be answered as yes, no, n/a except for question 40 and 41. I am storing the answers in a table using a column for each questions. For example, table answers:

userid (unique)
companyid (unique)
q1
q2
q3
...
q100

The answers table will only have a unique row per company. If an user edits the questionaire then the row will be transfered to a history table and a new row will be inserted again. If I use a table for questions or answers then for each company, I will have 78 or 100 rows of data with the answer( in this case the answer will be from a dropdownlist with yes, no, and n/a as the only options.

I would like to know if there is a better way to implement this since i need to implement 3 different sql statements (questionaire incomplete, complete or questionaire with at least one answer being set as "No") or instead of using statement to identify each questionaire should I add a new column with status and work with the code behind?

I tried to be as clear as possible and any adviced will be appretiated.

Answer 1

I prefer creating a table which stores the userid, questionid and answerid (or answer itself). This way, you can easily introduce new questions into the system without having to modify the table structure. It should also make it easier to determine if anyone has answered at least one question.

There are some potential drawbacks, just depends on the situation.

Answer 2

You need to Normalize your data. There are a number of levels of normalization, but typically its a good idea to aim for 3rd Normal Form for starters. Here is a tutorial on the first 3 normal forms.

In your case you probably don't want questions to be columns, because questions might have attributes like 'question text', and 'question type', and because its likely people will want to add and edit questions later.

Consider making a Questions table, with each question being a record in that table.

The Answers might be another table, with a foreign key pointing back to the question that is being answered, and a column for the actual answer, for example. There will likely be foreign keys back to user and company also.