Question

我在网络上为质量控制清单工作。我已经设立了一个桌子,但我很抱着我们的模式是次乐观的,我可以取得一些更好的业绩。请不要说我是用我方的q子,这样我才仅限于其能力。

每个清单都有数十个问题,有时是数百个问题。每一个问题都有2至10个可能的答复。每一个问题都是一个变体,每个问题都是答案。一份完整的清单是所有问题都与其可能的答复之一有关——选择一个答复。

核对清单的目的不同,可以随时间变化。为了在我们希望改变新的清单时保持已完成的核对清单,我们有模板。模板、问答表是核对清单、问答的镜子,是清单的当前版本。

因此,表层看着这一点。

<> 客户

Templates
- TemplateQuestions
  - TemplateQuestionAnswers
Checklists
- ChecklistQuestions
  - ChecklistQuestionAnswers

由于我们不想改变目前的模板,以便及时回头并修改已完成的核对清单,因此,当用户开始新的清单时,数据从模板复制到核对清单。

你可以猜测,这造成了许多重复。在约100万个答复网中,只有4 000个不同的答案。当然,定时问题安索尔也有重复,但不是坏。

因此,我认为我要做的是为核对清单模板建立一个版本系统,因此,我只能把独一无二的答案储存在空间上。这样,我就能够把清单与模板的version联系起来,而不是重复案文的全文,然后选定一个清单,回答问题。

在这方面,我刚才所勾画的内容。

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

等待的废墟是保证核对清单的安索斯底部与适当的问答配对——其核对表母的模板中存在的关系。

换言之,核对表Answers的每一行都必须反映一个问题,即从定时问题到从温克问题安索塔的一个儿童问题,构成在核对清单中转换的模板。我试图思考如何做到这一点,而我的想法进程却很短。这实际上是数据库的可交付性——完成的清单——因此,所有其他模板和所有内容都属于这种格式或摘要。如果我能做这项工作,我就错过了整个点!

这似乎是一个“little unwieldy”,因此我很想知道,如果我提出解决办法,其复杂性不值得我从执行中得到的节省空间。

同样值得注意的是,我简化了这个轨道。还有其他复杂方面,例如将问题分类为报告一类的制度,但我认为,我们无需加入。

Answer 1

我的理解是:

A simple improvement in what you are doing might be to use 3 tables for templates and only 2 tables for actual checklists: Checklist ( foreign key to a version of a template used ) Answer ( foreign key to checklist, foreign key to 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>

ps. If questions share answers, and they probably do in many cases ("yes", "no" comes to mind), you can have a table for unique answers: templateAnswers and a table templateAnswerUsage ( foreign key to template answer and foreign key to templateQuestion). This way you have no duplication of answer text. It s essentially many to many relationship between questions and answers. This might or might not make sense depending if the answers average size is bigger than the size of IDs you will use.

友情链接