CODEWITHSUNDEEP
databasedatabase-design
toomuchcs
toomuchcs

Reputation: 1364

Database design: hold values twice?

I have to design a questionnaire system. We've been discussing a lot of things together, including how to hold answers, and how to hold the possible answers. Now, I've just given in to my collegue though I do disagree. I just want to know your opinions about it and why. For me it's just a very bad idea to hold the same information twice:

We have a table question_answer, that just holds a question Id and an answer TEXT. In another table, we hold answers, also with a TEXT field.

My idea was to just link a possible answer to the answer's table by id, and if it was some kind of different answer, like an open question, I would put it in a text field that is optional...

Upvotes: 1

Views: 119

Answers (1)

richj
richj

Reputation: 7529

I would probably start from the table that is going to hold the most data - which in this case is likely to be the survey responses that link questions to answers for a given interview (which I have just modeled as a pair of Respondant and Representative foreign keys).

The Surveys table links questions to standard answers. I've put optional answers into the Responses table, but they could go into the Answers table instead (or it could be a view onto Standard answers plus Optional answers tables, however you prefer).

Questionnaire Schema

Upvotes: 2

Related Questions