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.
Your problem damands a different setup:
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.
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.