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.
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.
To use Cake parlance:
A Checklist HasMany Tasks
A Task BelongsTo Checklist
A Task HasMany Results
A Result BelongsTo Tasks
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
In this case:
Checklist HasMany Tasks
Checklist HasMany Results (< not sufficient to describe the relationship!)
Task BelongsTo Checklist
Result BelongsTo Checklist
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.
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.
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.
I would break the Checklist task relationship into a relationship table. And add a column to handle indicating the checklist has been updated. A replacement_checklist_id would work.
Checklist -< checklist_task >- tasks
Then when a checklist is updated, build a new set of entries in the checklist_task list order_in_list should be migrated to checklist_task as the task order may change on checklist revisions.
Task revisions should cause a replacement of the checklist with a new version.