存储清单和结果的数据库设计 [英] Database Design for Storing Checklists and Results
问题描述
I've already read these questions but the answers are either irrelevant or unsatisfactory.
我有一些清单用作经常重复的程序的指南。随着每个过程的改进和改进，检查列表的发展缓慢。
I have a number of checklists that are used as a guide for frequently repeated procedures. The checklists evolve slowly over time as each procedure is refined and improved.
我正在寻找的是存储一些不同的清单，每个清单都包含任意的，有序的要完成的任务数。该系统的用户将能够创建一个新的实例的清单，并勾选任务，因为它们在给定的实例。此外，我需要存档每个清单的个别实例以进行历史记录保存，因此我可以返回看看给定列表中的完成内容（按什么顺序，由谁等等）。 （为了简单起见，我已经排除了下面的这些元字段。）我也希望能够随时间修改每个清单上的任务，而不会破坏历史实例。换句话说，这些清单作为创建和使用新实例的模板。
What I'm looking to do is store a number of distinct Checklists that each contain an arbitrary, ordered number of Tasks to complete. Users of the system would be able to create a new Instance of a Checklist and tick off Tasks as they go on that given instance. Additionally, I need to archive individual Instances of each Checklist for historical record-keeping so I am able to go back and see what was completed (in what order, by whom, etc) on a given list. (For the sake of simplicity, I've excluded these "meta" fields below.) I would also like to be able to modify the Tasks on each Checklist over time without corrupting the historical Instances. in other words, these Checklists serve as "templates" from which new Instances are created and used.
这表示了有趣的数据库设计挑战，因为您不能简单地链接结果记录到任务记录的ID，它是一个实例。例如，如果您想要更改每个任务记录中的文本以改进清单并保持准确的结果，以下模式将不会工作：
This presents the interesting database design challenge because you can't simply link the result records to the ID of the Task record it is an instance of. For example, the following schema will not work if you want to be able to change the text in each Task record to improve the Checklists while maintaining accurate results:
Checklists
int(11) id
varchar(255) title // Text title of Checklist. I.e: "Household Chores"
Tasks
int(11) id
int(11) checklist_id // Which Checklist this Task belongs to.
int(11) order_in_list // Sort order for Tasks within a Checklist.
varchar(255) text // Text of the Task. I.e: "Take out garbage".
Results
int(11) id
int(11) instance_id // Groups a set of tasks into a historical Checklist instance.
int(11) task_id // Which Task this row is an instance of. Pull the text and order from here.
tinyint(1) checked // Whether the given instance has been completed or not.
使用蛋糕说法：
-
一个清单有很多任务
A Checklist HasMany Tasks
任务属于清单
一个任务有很多结果
结果属于任务
稍微修改将是在结果中创建每个任务的完整副本。这允许我们保留由instance_id分组的历史集合，以表示包含填充位的单个清单实例。
A slight modification would be to create a complete copy of each Task in the Results. This lets us keep historical "sets" of Tasks grouped by an instance_id to represent a single Checklist instance including the filled in bits.
Results
int(11) id
int(11) checklist_id
int(11) order_in_list
varchar(255) text // Store the full text of the Task in each result instance!?
int(11) instance_id
tinyint(1) checked
在这种情况下：
-
清单HasMany Tasks
Checklist HasMany Tasks
（<不足以描述关系！）
Checklist HasMany Results (< not sufficient to describe the relationship!)
任务属于清单
结果属于清单
乍一看，完全复制文本和每个任务的顺序似乎很浪费，但是我不能想出一个保留历史清单实例文本的好选择。
At first glance, this seems wasteful to completely copy the text and the order of each Task, but I can't come up with a good alternative that preserves the text of historical checklist instances.
目前我的想法是关系数据库可能不是这个的最佳解决方案问题，但我对文档数据库（如Mongo或Couch）的经验有限。这些会对历史清单数据提供更好的存储机制吗？这似乎具有将清单或实例的所有数据逻辑分组到单个文档记录中的优点。
Currently my thinking is that a relational DB might not be the best solution for this problem, but I have limited experience with document DBs like Mongo or Couch. Would these present a better storage mechanism for historical checklist data? This seems to have the advantage of logically grouping all of the data for a Checklist or an Instance into a single document record.
想法？
推荐答案
您基本上需要保持任务和清单只读。任何修改意味着新的任务或清单ID。否则，从设计的角度来看，模型应该很好。
You basically need to keep your tasks and checklists read-only. Any modification means a new task or checklist ID. Otherwise, from a design point of view, you should be fine with the model.
这篇关于存储清单和结果的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
