Database Design for Storing Checklists and Results

Question

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.

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.

Thoughts?

Answer 1

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.

Answer 2

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.