Let’s build further changes into the data model, which I created in my earlier blog post, such as having an automated approach to assigning an instructor and vehicle to a lesson, invoicing to customers and tracking of them.
First off, I need to build logic on the application side to assign an instructor and vehicle to lessons before they actually take place. The main thing to ensure here is availability, i.e. an instructor or vehicle can be assigned to a lesson only if both of them are available on the scheduled time of the lesson.
I need to construct two separate tables to keep track of occupancy for instructors and vehicle respectively. You might be wondering why I intend to keep track of occupancy instead of availability. The answer is, if we track occupancy instead of availability, then we do not need to create more tables to store the unavailability of resources due to leave planned by instructors or some scheduled service for vehicles. In-case of planned unavailability, records are inserted into occupancy tables accordingly.
I am making an assumption here that instructors and vehicles are only available during business hours, say 8:00 AM to 6:00 PM, on business days defined by the school. Therefore I can say that an instructor is available in a specified time on a business day if I do not find its occupancy detail for the specified time and day in the
The structure for table
staff_occupancy is as follows:
Some variations can be put in on an as-needed basis. For example, there should be at least a 15 minute gap between two subsequent lessons for an instructor.
The structure for table
vehicle_occupancy is as follows:
Allocation of instructor and vehicle are recorded in the
reservation table. I had already added two columns,
vehicle_id, into this table. These allocations will obviously, happen based on their availability.
In addition, I will keep
reservation_id in the
vehicle_occupancy tables, so that in case of cancellation of a lesson, the relevant occupancy of staff and vehicle can easily be released. But I will keep both of these columns as nullable as occupancy of instructors and vehicles will not necessarily be because of reservations. What if an instructor goes on a leave? Or one of the vehicles goes into the service center for a day?
In order to allow soft delete in such scenarios, I will add one column called
is_active in both of these tables.
For the invoicing requirement, I will create one table, namely
invoice, to hold invoicing details including
reservation_id. Here, invoicing is to be done to customers but based on the lessons carried out for the customer. Thus we need the
reservation_id column in the invoice table as well, so that at any given point in time, one can pull a report on detailed invoicing on based on a reservation for a customer. I will also create one column, namely
invoice_status_id, in the table to store status of invoices.
Here is the updated database structure designed in Vertabelo:
By now you must have started believing that data modeling for a reservation system of a driving school is as interesting and charming as learning to drive?
Feel free to post your questions and suggestions regarding the article. I am more than happy to answer them and incorporate your suggestions into my next article.