3

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?

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405

1 Answers1

4

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:

enter image description here

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:

enter image description here

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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I think that the `UNIQUE KEY` was correct as you had it initially: `(sessID, queID, saOrder)`. The order is per question, not per session. – ypercubeᵀᴹ Jan 31 '12 at 16:39
  • @ypercube I agree. The order is per question. How would I handle an additional table that has user answer selections? It seems to me it would also be (sessID, queID, ansID, user-selection-order), with (sessID, queID, user-selection-order) as unique, and ansID identifying the answer they selected. (sessID,queID) would map to SessionQuestions, (queID,ansID) to Answers as needed. Is there a better way to do that? – Explosion Pills Jan 31 '12 at 16:45
  • @tandu: I would think it would be `(sessID, queID, ansID)` with PK: `(sessID, queID)` and the whole `(sessID, queID, ansID)` FK to `SeesionAnswers` – ypercubeᵀᴹ Jan 31 '12 at 16:49
  • @ypercube This key would allow two questions to occupy the same "slot" in ordering. – Branko Dimitrijevic Jan 31 '12 at 16:50
  • Or `(sessID, queID, userselectionOrder)` with PK `(sessID, queID)` and the whole `(sessID, queID, userselectionOrder)` FK to `SeesionAnswers` – ypercubeᵀᴹ Jan 31 '12 at 16:51
  • @BrankoDimitrijevic: I think that the `saOrder` is meant to store the order of the Answer within a Question. – ypercubeᵀᴹ Jan 31 '12 at 16:52
  • @tandu The {sessID, queID, user-selection-order} key would allow two questions to occupy the same "slot" in ordering. I don't think this is what OP wanted. – Branko Dimitrijevic Jan 31 '12 at 16:52
  • @ypercube There is only one answer per question in a given session, therefore, there is no "order". Order is per answers on *different* questions. At least that's how I understood the situation, but I might be wrong ;) – Branko Dimitrijevic Jan 31 '12 at 16:54
  • @BrankoDimitrijevic tandu is the OP :). I agree about (queID, ansID) mapping .. there's no need to have a less restrictive mapping (sessID, queID, ansID). As for two questions occupying the same slot per Answer Selections, that should be a possibility. I want to store the user's selection as well as every time they change their answer. That being said, is my proposal correct? It seems a bit wasteful not to use SessionAnswers that already has most of the same information .. any academic defense not to do so? – Explosion Pills Jan 31 '12 at 16:56
  • @BrankoDimitrijevic Questions within a session need order, and answers within a question need order. It's both. – Explosion Pills Jan 31 '12 at 16:56
  • @tandu: Order of questions within a session should be stored in `SessionQuestion` – ypercubeᵀᴹ Jan 31 '12 at 17:01
  • @ypercube Yes, I was planning to do that. That shouldn't have an impact on SessionAnswers or Answer Selections, though. – Explosion Pills Jan 31 '12 at 17:02
  • Sorry OP, err... @tandu ;) Do you want to keep a history of answers or just the "latest version"? Are you actually using the saOrder as a kind of "version tag"? If yes, then I guess this is different from how I understood the question and some of the proposals above might be justified... – Branko Dimitrijevic Jan 31 '12 at 17:05
  • @BrankoDimitrijevic I do want to keep a history of all chosen answers. However, since answer order can be randomized, I think saOrder needs to be on a separate table from user-selection-order (or whatever I'd call it). saOrder is just the order answers are presented for a question for a given session and is immutable once defined. A user can change their answer as many times as they want, but I want to store each change. – Explosion Pills Jan 31 '12 at 17:08
  • @tandu Edited the answer... does that better suit your needs? – Branko Dimitrijevic Jan 31 '12 at 18:51