Наборы наборов наборов? Или, реализуя управление версиями для наборов наборов

Question

Я работаю над веб-приложением для контрольного списка контроля качества. У меня уже есть настроенный стол, но я догадываюсь, что наша модель неоптимальна, и я мог бы получить лучшую производительность. Пожалуйста, не то, что я использую mysql, поэтому я ограничен его возможностями.

Каждый контрольный список имеет десятки, а иногда и сотни вопросов. Каждый вопрос имеет от 2 до 10 возможных ответов. Каждый вопрос представляет собой строку varchar, как и каждый ответ. Заполненный контрольный список - это когда все вопросы связаны с одним из возможных ответов - когда выбран один ответ.

Контрольные списки различны для разных целей и со временем могут меняться. Поэтому, чтобы избежать непреднамеренного изменения заполненных контрольных списков, когда мы хотим вносить изменения в новые контрольные списки, у нас есть шаблоны. Шаблоны, вопросы и ответы являются зеркалом контрольных списков, вопросов и ответов и представляют "текущую версию" контрольного списка.

Таким образом, иерархия таблиц выглядит следующим образом

, клиент

• Шаблоны
  • TemplateQuestions
    • TemplateQuestionAnswers
• перечни
  • ChecklistQuestions
    • ChecklistQuestionAnswers

Поскольку мы не хотим, чтобы изменения в текущем шаблоне "возвращались в прошлое" и изменяли заполненные контрольные списки, данные копируются из шаблонов в контрольные списки, когда пользователь отправляется для запуска нового контрольного списка.

Как вы можете догадаться, это создает много дублирования. В ChecklistQuestionAnswers из примерно миллиона строк ответов имеется только 4000 отдельных ответов. Конечно, TemplatesQuestionAnswers также имеет дублирование, но не так плохо.

Поэтому я думаю, что хочу создать систему контроля версий для шаблонов контрольных списков, чтобы я мог сэкономить место, сохраняя уникальные вопросы с уникальными наборами ответов только один раз. Таким образом, вместо дублирования текста оптом, я могу просто связать контрольный список с версией шаблона, а затем набор контрольных списков - это какой ответ был выбран для какого вопроса.

Вот что я набросал до сих пор.

У клиентов есть много шаблонов. Шаблон имеет много ревизий, но только одну текущую ревизию. Каждая ревизия имеет много вопросов, и на каждый вопрос есть много (от 2 до 10) ответов. Каждый контрольный список относится к одному шаблону. Каждый контрольный список имеет набор ответов, которые указывают выбор ответа для каждого вопроса в его версии шаблона.

Questions /* all unique question wordings */
Questions.id
Questions.question

Answers /* all unique answer wordings. */
Answers.id
Answers.answer 

Templates 
Templates.client_id /* relates to client table. */
Templates.template_name 
Templates.current_version /* this is related to TemplateVersions.version_number */

TemplateVersions /* A logical grouping of a set of questions and answers */
TemplateVersions.version
TemplateVersions.template_id /* relates this version to a template. */

TemplateQuestions
TemplateQuestions.template_version /* relates a question to a template version */
TemplateQuestions.question_id /* relates a unique question to this template version */
TemplateQuestions.id

TemplateQuestionAnswers
TemplateQuestionAnswers.template_question_id /* relates this answer to a particular template version question */
TemplateQuestionAnswers.answer_id /* relates the unique question to a unique answer */
TemplateQuestionAnswers.id

Checklists
Checklists.id
Checklists.template_version /* relates this question to a template version -- associating this checklist to a client happens through this relationship */

ChecklistAnswers /* ( I might call this something other than 'Answers' since the lack of ChecklistQuestionAnswers breaks 'name symmetry' with TemplateQuestionAnswers ) */
ChecklistAnswers.checklist_id 
ChecklistAnswers.question_id
ChecklistAnswers.answer_id

Проблема, от которой я зацикливаюсь, гарантирует, что ChecklistAnswers связывает правильную пару вопросов и ответов - отношения, существующие в версии шаблона, на которую ссылается родительский элемент Checklist.

Другими словами, каждая строка в ChecklistAnswers должна "зеркально отражать" вопрос_ид из TemplateQuestions на один дочерний вопрос из TemplateQuestionAnswers, формировать template_version в контрольных списках. Я пытаюсь придумать, как это сделать, и мой мыслительный процесс здесь замыкается. Это действительно "результат" базы данных - заполненный контрольный список, - так что все остальные шаблоны и все остальное вроде эпифеноменального или абстракции этого. Если я не могу заставить это работать, я пропустил весь смысл!

Это кажется немного громоздким, поэтому мне интересно, принимаю ли я решение, сложность которого не стоит той экономии места, которую я мог бы получить от ее реализации.

Также обратите внимание, я немного упростил это. Существуют и другие аспекты сложности, такие как система категорий для группировки вопросов для составления отчетов, но я не думаю, что нам нужно вдаваться в это здесь.

Answer 1

Насколько я понимаю:

Простое улучшение в том, что вы делаете, может заключаться в использовании 3 таблиц для шаблонов и только 2 таблиц для реальных контрольных списков: Контрольный список (внешний ключ к версии используемого шаблона) Ответ (внешний ключ к контрольному списку, внешний ключ к templateAnswer)

Поэтому, если вы хотите получить список ответов для конкретного контрольного списка, вы должны:

select  <whatever columns you like>
from checklist c, answer a, templateAnswer ta, templateQuestion tq
where  a.checklist_id = c.id AND a.ta_id = ta.id AND ta.tq_id = tq.id AND
c.id = <something>

пс. Если вопросы имеют общие ответы, и они, вероятно, часто встречаются ("да", "нет" приходит на ум), у вас может быть таблица уникальных ответов: templateAnswers и таблица templateAnswerUsage (внешний ключ для ответа на шаблон и внешний ключ для шаблона Question). Таким образом, у вас нет дублирования текста ответа. По сути, это много-много отношений между вопросами и ответами. Это может иметь или не иметь смысла в зависимости от того, превышает ли средний размер ответов размер идентификаторов, которые вы будете использовать.