Database Model for a Driving School’s Reservation System. Part 2

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 staff_occupancy table.

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, staff_id and vehicle_id, into this table. These allocations will obviously, happen based on their availability.

In addition, I will keep reservation_id in the staff_occupancy and 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.

Invoicing

For the invoicing requirement, I will create one table, namely invoice, to hold invoicing details including customer_id and 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.

Database Model

Here is the updated database structure designed in Vertabelo:

Conclusion

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.