0

I'm working on a question/answer project and having a hard time coming up with a suitable database structure.

Each question can have from 1 - "n" answers, and each answer is associated with one of 6 predefined categories.

For example:

Q1) Which magazine would you prefer to read?

  • Science (+1 for Science category)
  • Wired (+1 for Science, +1 for Tech)
  • Vogue (+1 for Fashion)
  • Time (+1 for Information, +1 for Social)
  • Fortune (+1 for Business)
  • People (+1 for Social)

Q2) Have you taken an art class?

  • Yes (+1 for Artistic)
  • No (+0 for Artistic)

Q3) What's the best part of a movie?

  • Action (+1 for Science)
  • Story (+1 for Social)
  • Music (+1 for Artistic)
  • Acting (+1 for Artistic)

I know I should have one table for Questions, and a separate table for Answers linked by question_ID, but I'm not sure how to deal with a variable number of answer choices that are different for each question, and their corresponding categories.

Improve this question
2

1 Answer 1

Reset to default
1

Consider a data model like this:

ERD

You have a list of quesitons in the QUESTION table. Each question can have two or more possible options for answers in the OPTION table. For each option there are one or more category score changes which are noted in OPTION_CATEGORY. When a USER takes the quiz their answers (i.e. choices of options) are recorded in ANSWER.

Note that with this design you can change someone's score in multiple categories with a single answer (such as in your example of reading WIRED magazine) and you also have the option of changing someone's score in a category by an increment other than 1 (as in your example of not having taken an art class). You could even change someones score by more than 1, for example in your question 1 you could score +10 in the science category for prefering to read Philosophical Transactions of the Royal Society!

Improve this answer
5
  • How would I specify the answer choices in OPTION? I'm thinking: Create a new row for every answer choice of every question (which could lead to hundreds of rows), and each answer choice is is tied to QUESTION with a foreign key. Then when populating a question, query for all answers associated with it.
    – Erich
    Sep 10, 2012 at 14:19
  • @Erich - Yes, that's right. Every question is a row in QUESTION, every choice for an answer is a row in OPTION. When a user actually picks an answer to a question, that becomes a row in ANSWER. All of the tables are linked with foreign keys. Wherever you see a crows foot in the diagram, that indicates there is a foreign key on that table to record the relationship to the parent table. When the crow's foot has an "I" beside it, that indicates that the FK is also part of the primary key (PK) for that table. Don't be scared by hundreds or thousands of rows. That is what DBMS is for!
    – Joel Brown
    Sep 10, 2012 at 15:45
  • "FK is also part of the primary key (PK) for that table." Are you referring to creating a composite key here? Any particular reason to require a composite key rather than just a PK or no PK at all?
    – Erich
    Sep 10, 2012 at 18:01
  • @Erich one of the pitfalls of having cyclical relationships is that you will want to have constraints in place to prevent semantic inconsistency. Specifically, you need to be sure that the question FK on ANSWER points at the same question as through the relationship to OPTION (and on through to QUESTION). This is easier to do when you have the question ID in the answer table. Also, for pure intersection tables (like ANSWER) lots of designers prefer to have a composite PK rather than creating a surrogate PK. This is a hotly debated subject so your mileage may vary.
    – Joel Brown
    Sep 10, 2012 at 18:17
  • @Erich - Regarding why not "no PK at all" - I would think you'd want to avoid a situation where a user can answer the same question multiple times. Having a PK that limits each user to one answer for each question is an easy way to avoid duplicating (or recording inconsistent) answers.
    – Joel Brown
    Sep 10, 2012 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.