rental car domain model
As part of a recent job application, I was asked to create a database model of a rental car company. The company, inexplicably, did not yet have an application and I was tasked with modeling the database to enable users to rent cars.
Without giving away the entire exercise, the basics are, a car rental company has several locations and a wide range of vehicles types (SUVs, trucks, convertibles, etc.). A customer places a reservation and a rental agreement is made when the customer picks up the rented vehicle. Finally, extras (car seats, GPS, etc) can also be added to the rental.
While at Flatiron School, my classmates and I had several assignments that dealt with database modeling. Most of them were small, three models, with simple relationships between them. During our first group project, a Rails app that connected users and aimed to reduce food waste, we spent an entire afternoon discussing the schema and the different relationships our models would have. The time spent early on in the development of our application was incredibly beneficial. The changes we ended up making to the database were minor, and we were able to focus on the functionality of the application during the short period we worked together.
When I began the car rental database, I first thought about the nouns and verbs from the instruction of the exercise. Nouns like vehicles, locations, and extras would be the different tables of the database. I decided on the following seven tables:
- vehicle
- vehicle_type
- user
- reservation
- location
- invoice
- extra
Mapping the relationships between the entities was more difficult, and the principal challenge of the exercise.
While I missed having a teammate to discuss the potential relationships, I knew that if I carefully thought about the potential associations between each model and their real world application, then I could successfully model the database. I began by listing all the tables and looked for the verbs used in the instructions. This helped me represent the connection between the tables. For example, the instructions stated that a user indicates a vehicle category while making a reservation. The word ‘indicates’ led me to believe that a reservation had one vehicle type, or that the reservation belongs to the vehicle type. With all the tables listed, I started connecting the tables and notating the relationships. I also thought about when it made sense to give one model access to the information of another model.
I created the table relationships as follows:
In this specific exercise, the vehicle would be picked up and dropped off at the same location. Additionally, each vehicle is part of a specific category. Thus, vehicles belong to a location and belong to a vehicle_type. Lastly, vehicles will be rented many times and should know about their invoices, they have many invoices.
There are a number of different types of SUVs, so a vehicle_type has many vehicles. The exercise stated that a user would indicate a specific vehicle_type during the reservation, so the vehicle_type has many reservations.
The customer, or user in my model, begins a reservations and ultimately receives a rental agreement in the form of an invoice. The user has zero, one, or many reservations and invoices. At this point, the application is small, and the user can only reserve and rent cars. In the future, the user should be associated with more models.
Each reservation is made at a single location, by a single user and begins by selecting a single vehicle_type from a collection. The reservation belongs to these three models.
I figured each location of the rental car company existed independently, so this table had the most relationships. The straightforward relationships were that a location has many vehicles, reservations, invoices and extras. I assumed that a location would like to know about its users. Since a location has many reservations and each reservation belongs to one user, I concluded that the connection exists through its reservations.
Lastly, there was the component of this invoice, which acts as a rental agreement, highlighting the important details of the transaction, like start and end dates. The invoice table is similar to the reservation one. An invoice belongs to a specific user at one location. The difference is the invoice knows about the vehicle that is rented. The invoice would also include any extras the user selected. These belong to the invoice as an additional charge.
Extra items belong to the invoice and to the location. Each location would have its own set of extras. Additionally, when a vehicle is rented the extra would be unavailable until the rental was completed.
I enjoyed the exercise and thought my approach and solution coincided with the minimal requirements laid out by the instructions.
