Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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. first draft

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.

second draft

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.

share|improve this question
Naming all your columns with id is kind of confusing. – ypercube May 14 at 11:32
I had to add at least one column to add the foreign keys, which is a restriction of MySQL Workbench. So I chose a simple id. Also it doesn't show the foreign keys as columns, but as labels for the relations. – taymless May 14 at 11:37
What are you going for with subcategories? Is the idea that if you pick a category all of its descendants come along automatically, but sometimes you want just a subcategory and not a whole category? Also, how were you planning on distinguishing between a template and an instance of a checklist in your second model? – Joel Brown May 14 at 13:16
1) Right, sometimes I want the checklist to only have "top level" categories, and every now and then I want one of these to have subcategories. 2) The checklist_data table would have a reference to all the instances. The checklist table would have all checklist configurations, with the option to mark it as a template. Thats what I meant with the is_template above. – taymless May 14 at 13:45
add comment (requires an account with 50 reputation)

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.