Checklist architecture questions
I had some questions about how to set up a series of checklists for a given item. Each item goes through a process and we want to track various steps for each process. We currently have two checklists implemented and we have 4 or 5 more we want to add. Now seems like a good time to possibly redesign these lists (if needed). The current lists are just checkbox sets on a layout, set to a field in the main Item table, using a value list generated from a Constants table field, which is just a return separated list of text. So I was thinking of separating the checklists from the main Item table.
I was thinking of possibly two new tables: one for checklists, one for checklist line items (the values to display on each type of checklist).
Items::ItemID = Checklists::fk_ItemID
Checklists::List_Type = ChkLst_Values::List_Type
(You could build the value list dynamically from ChkLst_Lines using the List_Type field. Hmmm…maybe a third table for individual list line item responses, instead of a combination field "List_Values" in the "Checklists" table.)
One problem I see with doing it this way is maintenance. To create the supporting checklists a script would have to fire, either at the creation of a new Item record, or perhaps run it as a script trigger on "RecordLoad" when using the layout to view the checklist (checks if it exists, creates it if not).
It hasn't been requested yet, but I can foresee that this is something that we might want to let certain end-users administer (the values on the checklist). Putting them on a separate table would easily allow that...but just letting them edit a Value List would work, too. One other consideration is that we may want to create a sum or calculation to display (on a different layout) how much of a checklist has been done. Is that possible to do on a value list? Or would the separate line item table be the best way to do that?
Another feature that I would like to provide is a button to check all line items; experienced users may not sit there and tick them off one by one, but want to record them all at once. How do you parse through a combined value list? (If there was a third table of just responses iterating through those and setting the values with a script would be pretty straightforward.)
What are the benefits of separating the checklists? I know that it would slightly unclutter the main Item table (it already has 600+ fields), but is that really a problem? The flipside is that it would require creating new tables and T.Os, cluttering the relationship graph. :)
Any discussion or suggestions about this would be appreciated.