Выбор из нескольких ключей-кандидатов

Question

Мне может не хватать математических знаний, чтобы найти правильный ответ для себя. У меня есть таблицы, настроенные так (нерелевантные столбцы опущены):

Questions
   queID

Answers
   queID
   ansID

Пользователи могут создавать тесты, выбирая определенные вопросы. Ответы - это возможные ответы, которые пользователи могут выбрать для каждого вопроса.

Sessions
   sessID

Когда клиент выбирает группу вопросов для ответа, создается "Сессия".

SessionQuestions
   sqID
   sessID
   queID

Это вопросы, которые пользователь выбрал для данной сессии.

Где я бью загадку на SessionAnswers уровень. Порядок ответов на вопрос может быть случайным. Таким образом, вопрос с множественным выбором с порядком ответов по умолчанию A,B,C может отображаться пользователю как C,B,A. Однако всякий раз, когда они снова рассматривают этот вопрос, он все равно должен быть C,B,A, так что окончательный порядок должен быть сохранен. Моя предварительная таблица такова:

SessionAnswers
   sqID
   ansID
   saOrder

Дело в том, что sqID указывает на queID, но так же и ansID. Это означает, что я мог бы использовать sessID на этом столе или sqID, Я не уверен, что выбрать. Эта настройка по-прежнему позволяет сопоставить ansID с ответом, сопоставленным с вопросом, которого нет даже в SessionQuestions, что было бы неверно. Могу ли я улучшить настройку, чтобы избежать этого?

Answer 1

Зависимости в форме ромба выигрывают от естественных ключей (в отличие от суррогатов). Естественные ключи могут "сливаться" в нижней части "ромба", создавая правильное поведение ссылочной целостности.

В вашем случае, используя естественный ключ в SessionQuestions ({sessID, queID} в отличие от суррогата {sqID}), позволяет распространять все "компоненты" родительского ключа по обоим "краям" ромба, делая невозможным получение ответа на сеанс, вопрос которого не находится в том же сеансе.

Ваша модель должна выглядеть так:

ПРИМЕЧАНИЕ. Так как ответ за сеанс может быть идентифицирован также по порядку, вам потребуется один дополнительный альтернативный ключ в SessionAnswers (обозначается как "U1" выше). Обратите внимание, что ни queID ни ansID должен быть включен в этот ключ - в противном случае два разных ответа могут занимать один и тот же "слот".

--- РЕДАКТИРОВАТЬ ---

@tandu, я вижу, вы приняли мой ответ, поэтому я, вероятно, должен остановиться, пока я впереди;) но я все же хотел бы предложить альтернативный дизайн:

Это должно позволить вам сохранить историю и порядок. Когда пользователь вводит набор ответов, они записываются в SessionAnswers с версией =1. Если один или несколько ответов изменены, создается версия =2 и т. Д.

С каждой версией сеанса связано 2 набора:

• Набор уникальных вопросов, каждый с одним ответом (применяется через PK).
• Набор "слотов", используемых для упорядочения (применяется через альтернативный ключ).

И поскольку оба они содержатся в одной и той же таблице, это означает, что каждый вопрос отображается точно на один слот, а каждый вопрос - на один вопрос.

Кстати, это также позволяет вам менять порядок между версиями, если это необходимо.