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

I need to design a data model for a reservation system for a driving school. The subject area looks quite straightforward, but complexities are still involved. You have to track all requests from clients and keep track of resources (vehicle, time and instructor) consumed during lessons.

Introduction

I like to use a domain driven approach for designing a data model. It makes me put technology obsession aside and concentrate primarily on modeling the subject area revolving around its associated entities and relationships amongst themselves.

Requirements in a Nutshell

Let me first put down the requirement in plain English first.

I need a data model for a driving school to allow customers to make reservations for lessons online. The driving school may have more than one instructor and more than one vehicle. The instructor is assigned to the lesson upon reservation. The system should allow customers to cancel reservation/s any time before the scheduled day. The vehicle assigned to the lesson should also be recorded if the lesson takes place.

Entities and Relationships Involved

When I think of the subject, the entities which come first to my mind are, “Customer”, “Instructor”, “Driving Lesson”, “Reservation Request” and “Vehicle”. Let me start with my very first table for this model, and that is customer. It is to store master data for customers. I would probably need another table to store Instructor details, but instead of creating a table with the name of the instructor, I will create a generic table called staff for staff details and keep “Instructor” as a job title. It will make my data model extensible to cater to other service areas as well, like administrative and legal work, of a driving school.

I consider “driving course” as one of the services, thus I create another table called service. A service, “driving course” in this case, can have multiple lessons. To handle this requirement, I certainly need another master table, namely, lesson, and one relationship table, namely service_lesson, to manage many to many relationship between both of these master entities, i.e. one service can definitely have multiple lessons, but on the other hand, one lesson can also be part of more than one service.

When one submits a reservation request, s/he is asked to fill in his/ her details and preliminary preferences like what type of service s/he wants, choice for vehicle and start date. The customer’s details are stored in the customer table. Subsequently, one request is created in the request table, and all preferences are stored against the request in the same table. There are certain statuses associated with each request, like “submit”, “in-process”, “cancel” and “complete”. I will create one supporting table for it called request_status.

At the time of request submission, one puts a preference for vehicle, i.e. type of vehicle. However, the vehicle would actually be assigned to a lesson when it takes place. Therefore I keep vehicle_type_id as one of the columns in request table for now.

When a request is processed, reservations are made for each lesson of the service request. In addition, instructors and vehicles are assigned to each lesson based on the availability of instructors and customers’ preferences for vehicles. Lessons are scheduled for future dates with status “Open”. All of these details are captured in another transaction table called reservation. I have highlighted all transaction tables with a different color than all master tables.

One master table, reservation_status, is created to store all possible values for reservation statuses like “open”, “in-process”, “cancel” and “complete”.

This model allows a customer to cancel an individual lesson as well as the service request as a whole. If customer cancels the service request, then all remaining lessons, which are scheduled for the customer, are cancelled in the reservation table.

Please refer the data model created by me using Vertabelo for column level details of all these tables. A few points regarding column creation:

• A flag column named is_active is added to all master tables to enable soft deletion of records. So for example, if any instructor leaves the school, we will flip the flag to “N” to make his record inactive.
• Certain columns like created_date, created_by, last_modified_date and last_modified_by are added in all transaction tables to enable an audit trail for changes in records. Transaction tables are highlighted in blue in the data model created in Vertabelo.
• You might be wondering what the address_id column in the staff table is for. I have purposely put this column in the staff table so that I can extend my data model to support an automated process to assign instructor to a request based on his or her location. For example, suppose in New York City, a request from White Plains comes in. My system should first look for an available instructor in the same or closest vicinity. My system should never assign an instructor staying in Manhattan, which is almost 50 miles away from requester’s place.

Salient Features of This Model

• This model allows customers to put in reservation requests as per their preferences for start date and vehicle.
• It allows them to cancel one or more lessons of their course, or the entire course itself.
• This model captures instructor and vehicle details for each lesson.
• This model is extensible to handle all possible services provided by a driving school.
• It enables us to design training courses and plan lessons effectively.

Database Model

Here is the database design for our reservation system. The model was created in Vertabelo for Oracle database but the same design can be implemented for other database engines without significant changes.

Conclusion

There are certain subject areas that we didn’t cover in this article, such as:

• Can we build an automated approach to allocate vehicles and instructors to lessons?
• How about invoicing customers? What if a customer does not want to pay for the entire course but for couple of lessons of it? Can we make these lessons available to him?

Does our existing model support such features? The answer is NO. I will probably cover these subject areas in my next article.