mysql - 套套套套？或者，对集合实现版本控制

Question

我正在开发一个用于质量控制 list 的网络应用程序。我已经设置了一个表，但我有预感我们的模型不是最优的，我可以获得更好的性能。请注意，我使用的是 mysql，因此我受到其功能的限制。

每个 list 都有数十个，有时甚至数百个问题。每个问题都有 2 到 10 个可能的答案。每个问题都是一个 varchar 字符串，每个答案也是一个 varchar 字符串。一份完整的 list 是指所有问题都与其可能的答案之一相关联——即选择一个答案。

list 针对不同的目的而有所不同，并且会随着时间的推移而发生变化。因此，当我们想要对新的 list 进行更改时，为了防止已完成的 list 被无意中更改，我们有模板。模板、问题和答案是 list 、问题和答案的镜像，代表 list 的“当前版本”。

所以表层次结构如下所示

.客户端

• 模板
  • 模板问题
    • 模板问题答案
• list
  • list 问题
    • list 问题答案

因为我们不希望当前模板中的更改“回到过去”并更改已完成的 list ，所以当用户开始新的 list 时，数据将从模板复制到 list 中。

正如您所猜测的，这会产生大量重复。在 ChecklistQuestionAnswers 中，在大约 100 万行答案中，只有 4,000 个不同的答案。当然，TemplatesQuestionAnswers 也有重复，但没有那么糟糕。

所以我想我想做的是为 list 模板创建一个版本控制系统，这样我就可以通过仅存储一次带有独特答案集的独特问题来节省空间。这样，我就可以将 list 与模板的版本链接起来，而不是大量复制文本，然后 list 集就是为哪个问题选择了哪个答案。

这是我到目前为止所勾勒的内容。

A clients has many templates. A template has many revisions, but only one current revision. Each revision has many questions, and each question has many ( between 2 and 10 ) answers. Each Checklist relates to one Template. Each checklist has a set answers that indicate the answer select for each question in its version of the template.

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 中的 Question_id“镜像”到 TemplateQuestionAnswers 中的一个子问题，形成 Checklists 中的 template_version。我正在尝试思考如何做到这一点，但我的思维过程在这里出现了短路。这实际上是数据库的“可交付成果”——一个完整的 list ——因此所有其他模板和所有内容都是附带现象或抽象。如果我不能让这个工作，我就错过了重点!

这似乎有点笨拙，所以我想知道我是否正在制定一个解决方案，其复杂性不值得我通过实现它来节省空间。

另请注意，我对此进行了一些简化。还有其他方面的复杂性，例如用于对报告问题进行分组的类别系统，但我认为我们不需要在这里讨论这一点。

Answer 1

据我了解:

对您正在做的事情的一个简单改进可能是使用 3 个表作为模板，仅使用 2 个表作为实际 list : list (所用模板版本的外键)答案( list 的外键，模板答案的外键)

因此，如果您想要检索特定 list 的答案列表，您将:

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>

ps。如果问题共享答案，并且在很多情况下可能会这样做(想到"is"、“否”)，您可以有一个用于唯一答案的表格:templateAnswers 和表 templateAnswerUsage (模板答案的外键和模板问题的外键)。这样您就不会出现重复的答案文本。它本质上是问题和答案之间的多对多关系。这可能有意义也可能没有意义，具体取决于答案的平均大小是否大于您将使用的 ID 的大小。