After researching online, I have come up with the following database tables. Here are some information to assist with my approach.
-
Each vehicle has many service types.
-
Each vehicle will have it’s own set of check-list questions, to be answered after each service.
-
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.