mysql - Choosing from multiple candidate keys

Question

I may lack the mathematical background to find the right answer for myself. I have tables set up like so (irrelevant columns omitted):

Questions
   queID

Answers
   queID
   ansID

Users can create quizzes by picking certain questions. Answers are the possible answers users can choose from per question.

Sessions
   sessID

When a customer picks a group of questions to answer, a "Session" is created.

SessionQuestions
   sqID
   sessID
   queID

These are the questions a user selected for a given session.

Where I'm hitting a snag is at the SessionAnswers level. The order of answers for a question can be random. That is, a multiple-choice question with default answer order of A,B,C can be displayed as C,B,A to a user. Whenever they view that question again, though, it still needs to be C,B,A, so that final order needs to be stored. My tentative table is this:

SessionAnswers
   sqID
   ansID
   saOrder

The thing is that sqID points to queID, but so does ansID. That means that I could use sessID on this table or sqID. I'm not sure which one to pick. This setup still makes it possible for ansID to be mapped to an answer that is mapped to a question that is not even on SessionQuestions, which would be incorrect. Can I improve the setup to avoid that?

Answer 1

Diamond-shaped dependencies benefit from natural keys (as opposed to surrogates). Natural keys can "merge" at the bottom of the "diamond", producing the correct referential integrity behavior.

In your case, using the natural key in SessionQuestions ({sessID, queID} as opposed to surrogate {sqID}), enables propagation of all the parent key "components" down both "edges" of the diamond, making it impossible to have a session answer whose question is not in the same session.

Your model should look like this:

NOTE: Since per-session answer can be identified by order too, you need one additional alternate key in SessionAnswers (denoted by 'U1' above). Note that neither queID nor ansID should be included in that key - doing otherwise would allow two different answers to occupy the same "slot".

--- EDIT ---

@tandu, I see you accepted my answer so I should probably stop while I'm ahead ;) but I'd still like to propose an alternative design:

This should enable you to keep both history and order. When user enters a set of answers, they are recorded in SessionAnswers with version=1. If one or more of the answers are changed, a version=2 is created and so on...

Each session version has 2 sets associated with it:

• A set of unique questions, each with one answer (enforced through PK).
• A set of "slots" used for ordering (enforced through alternate key).

And since both are contained in the same table, this implies that each question maps to exactly one slot and each slot to exactly one question.

BTW, this also enables you to change the order between versions if needed.