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.
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.