I'm developing an app which is basically a collection of checklists that have to be processed. I obviously need a database in which the information has to be stored. I just can't wrap my head around designing the database model.
Following should be considered:
- A checklist-item describes what needs to be checked and should be able to take in different types of input, like simply checking off, radio input (yes/no), some textinput or a dropdownlist of possible values
- A category consists of 0..* sub-categories and/or 0..* items
- A checklist consists of 1..* categories
Obviously these 3 entities have more information like title and description for all of them or modified_by and modified_on for the items, but I'm going to leave that out to keep it short.
The categories, items and some checklists will be predefined and managed from a webinterface
- When an item or a category is updated or deleted, this changes should effect all active checklists that contain these
- A collection of checklist-templates has to be stored, to be provided in the app
In the app:
- Every user can create a checklist by selecting a checklist-template or combining pre-defined categories in the app
- The created checklist can then be worked on, which just means finishing all items
This is a rough mockup of the first draft I came up with.
Eventually I realized, that this doesn't work because there is no separation between the predefined data for reuse and the values that originate from working on a checklist. I figured I need to split the data. this is what I came up with.
This way I'd save the changing data for the checklists and items separately from the information that is being reused in multiple checklists. Also I'd add a
is_template column to the checklist which would enable me to save all predefined and user-created checklists in one table and possibly mark a checklist as a template afterwards, if proven useful.
Now to finish off with some questions: Is there a flaw that I'm missing in this approach? Does somebody know a better way to design this? Did it even become clear what I'm trying to do and why I'm having issues with this?
Please let me know. It's been a while since I did database modeling and this is supposed to be only the beginning of a larger tool, so it needs a good foundation.