Database design question

After researching online, I have come up with the following database tables. Here are some information to assist with my approach.

  1. Each vehicle has many service types.

  2. Each vehicle will have it’s own set of check-list questions, to be answered after each service.

  3. There’s a primary check-list table, with all possible questions.

I was wondering if there was a better solution, or additional modifications to be made before I start developing. Also, if you have done something similar, I would really appreciate your input.




TABLE vehicle

  id

  make_id


TABLE service

  id

  vehicle_id

  schedule_date


TABLE checklist

  id

  question (text)

  type_id  (some are check-boxes, some are drop-downs)


TABLE checklist_set (user who requests the service, must also select related checks from check-list)

  id

  vehicle_id

  checklist_question_id


TABLE checklist_response (table to hold all answers)

  id

  service_id

  checklist_question_id

  answer (selected value from checklist/type_id)

  notes (additional findings)



With this approach, a new question could be added to the check-list, and user will have it available for selection if they’re booking their vehicle in for servicing.

Thank you.

If I understood you correctly, you separated questions in one table, and you will just reference question ID to the desired servis questionare. If you add a new question, it will immediatelly appear on questionare.

I would do the same as you so it seems fine.