PL200 PL200 - 1 year ago 86
MySQL Question

Storing checklist results in MySQL

I've been racking my brains, but still can't think of a solution.

I'm creating a database for a checklist web app. Basically, a checklist is created and completed with options of either 'complete' or 'not complete', and then a report is generated which shows which items were not complete.

Currently, there is a table for a 'Checklist' that consists of many 'Checklist Items'. The results of the Checklist (whether they are complete or not complete) are to be stored in a table called 'Report', but this is where I need help. This is because I need to store whether they were marked as complete and not complete, and each checklist has a different length.

A possible solution is simply storing the unique ID of each 'Checklist Item' that isn't working in a string that could be stored in 'Report', with each ID separated by a comma. Each ID could then be extracted using PHP's 'explode' function, and this would probably work. However I don't think this is the most elegant solution, so any help to a better solution would be much appreciated.

EDIT (Important): The checklists are completed daily, and the reports are intended to be stored long term. Therefore adding a 'report' field to each item would not work.

Answer Source

Your problem damands a different setup:

  • a table for checklists (id, name of checklist, ...)
  • a table for checklist-items (checlistId, itemId, title, ...)

Now you can add a new table complete per checklist or per checklistitem. But for me a simple field on each of the above tables (TRUE or FALSE) should be enough.

EDIT

After reading the comments the table report should contain following fields - itemId - userId - (optional checklistId when items can be linked to multiple checklist) - (completed is always true)

A problem remains: what with items added to checklists after the completion. A better solution is to add tables for checklists in progress (with the user that started it) and a link to the current completed items.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download