I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio. I was trying to model a car rental company, where clients can rent cars, and I have to know which client has rented which car on which date, and also the date the car was returned. Designed as a simple reservation management system for SME vehicle rental agencies. Created as a test harness for our e-Bridge travel distribution product, but fully functional for use by operators, car, bike, bus rental agencies. OTA Vehicle is e-Bridge ready, so it can be connected directly to your website via an online booking engine.

1. Car Rental Database Template Downloads
2. Car Rental Database Template Software
3. Car Rental Database Template Spreadsheet
4. Car Rental Database

You might have rented a car on your last vacation. You reserved your car online, and then picked it up from its designated location after paying all the previously-agreed charges. Once you were done, you returned it to the agency and perhaps paid some additional fees. Did you ever think about the system that makes all these things happen? In this article, we’ll look at a data model for a car rental system.

Why Build Another Car Rental Data Model?

I want to design a data model of a fully functional system for an international car rental company. The company keeps vehicles to rent in various segments (mini, economy, intermediate, SUV, cargo and limousine). It operates its operations from various cities across multiple countries. The company allows their customers to rent a car from one location (pick-up location) and drop it off in another location (drop-off location).

At this point, let’s refer to an earlier article that explains a simple car rental company model. This model caters to all the fundamental services offered by a car rental company.

Before we add new functions, I would like to incorporate some minor changes into this model, namely:

• Adding city as a column in the location table, and removing the city table altogether.

• Adding one additional column, zip (as in ZIP code, or postal code), in the location table. This system will identify a pickup/drop-off location by its ZIP code. There are many countries where ZIP code is an alphanumeric number, so I will keep this column as a varchar column.

• Adding driving license issue date to the customer table. There are some countries where the maximum speed limit depends on when the license was issued to the driver.

• Renaming the category table to car_category , which describes its content more precisely.
• Storing a customers’ flight information if the pickup location is near an airport. This enables the system to make appropriate changes to the customer’s reservation request in case of flight delays or cancellations. To do this, I add another table called flight_detail and connect it to the reservation table.

Adding Customer Invoice Information

For invoicing, we need to store a rental value for each inventory item, including cars and equipment. The rental cost is assigned to each category, because the reservation process deals with categories rather than individual cars.

Let me add rental_value in the car_category and equipment_category tables.

PDF Expert - Edit and Sign PDF for PC and Mac. Written by Readdle Inc. Category: Productivity Release date: 2019-11-18 Licence: $79.99 Software version: 2.4.30 File size: 31.37 MB Compatibility: Windows 10/8.1/8/7/Vista and Mac OS X 10.12. PDF Expert 2.5.15 PDF Expert springs into action with smooth scrolling and fast search from the first document you select. PDFs open instantly, whether they are just small email attachments. Pdf expert 2.4.5 542 for macos sierra.

On similar lines, there must be some cost associated with insurance. This cost is determined by the insurance company. For now, I’ll add one more column, cost, in the insurance table.

For invoicing, I create a separate table to store all invoice details. This way, these same details can easily be retrieved whenever needed. Since the computation of these values is a bit tricky, I won’t repeat them again and again for an invoice. I’ll add one table, namely rental_invoice, that is primarily connected with the rental table.

The rental_invoice table contains the following columns:

• id – the primary key of this table.
• rental_id – the primary key of the rental table. I will add one unique constraint on this column: there can be only one record for each rental.
• car_rent – This column signifies the rental costs for the rented vehicle.

• This cost can be determined using the following SQL:

• equipment_rent_total – This column shows the amount to charge for any equipment rented out to the customer

• The total cost can be determined using the following SQL:

• insurance_cost_total – This column is for the customer’s total insurance cost. This can be determined by using the following SQL

• service_tax and VAT – As their names suggest, these columns store values for applicable service tax and VAT.

• total_amount_payable – This column will contain the value of the total invoice amount. This would be the sum of following columns:

  total_amount_payable = car_rent + equipment_rent_total + insurance_cost_total

• waiver_amount and net_amount_payable – These columns store values for waiver amounts (if any) and the net amount due for payment. The waiver_amount is how much will be waived off the total invoice. It is commonly used when a rental company offers a discount to customers. The formula for determining net_amount_payable looks like this:

  net_amount_payable = total_amount_payable – waiver_amount

Mobile Inventory – For a car rental company, its inventory is always mobile because it is moving from one location to another. If you’ve noticed a checkbox saying ‘returning to different location?’ when you reserve a car online, you’ve seen it in action. The system treats your request a bit differently if the returning location is NOT the same as pickup location. The system always keeps a track of its inventory as and when it is rented out and returned.

For example, one customer rents a car from Chicago, confirms that the drop off location will be different, and drives to his destination in Saint Louis. Obviously, he’ll drop off the car at the company’s Saint Louis location. In this case, as soon as he drives the car from the Chicago location, this part of the inventory is no longer tied up to that office. The car will get registered again, this time with the Saint Louis office, as soon as he is done with it.

To incorporate this mechanism, I will add one column, namely current_location_id, in the car table as well as the equipment table. This column holds only the valid IDs of locations from the location table.

Dr.Fone (Mac)- Data Recovery (iPhone Data Recovery) World's 1st Mac iPhone and iPad data recovery software. Recover iPhone data on Mac with three ways. Able to recover contacts, SMS, reminders, call history, photos, video, etc. Compatible with latest iPhone and Mac OS. Why Choose Dr.Fone - Data Recovery (iOS)? Dr.Fone is the first one who provides iPhone data recovery for personal use in the world. At Wondershare, we lead our industry in technology development with more than 8 years of experience in iPhone data recovery and more than 15 years of experience in data recovery. Editors' note: This is a review of the trial version of Wondershare Dr.Fone for iOS for Mac 4.1.1. Best VPN Services for 2020 Curated by Cnet See more on Cnet. Express VPN Best for privacy. Dr fone wondershare ios. Buy Dr.Fone (Mac) - iOS Toolkit to enjoy all iOS features on Mac on Dr.Fone official website here.

So, with the above example, the initial location of the car is Chicago; it will be updated after the customer returns the car to the destination office.

Setting Up Fueling Options

Most car rental companies give the following types of fueling options:

1. Fuel Service Advance – the customer pays for a full tank of fuel in advance, and returns the car with an empty tank.
2. Fuel Service Charge – the customer gets the car with a full tank of fuel, but pays for it based on fuel usage.
3. Fuel Self Service – the customer receives the car with a full tank of fuel and returns the car with a full tank. This is the most widely-accepted option of the three.

Here, we are not concerned about which option the customer chooses. What we want is to register their choice while processing the rental request.

To meet this need, I’ll add one table, fuel_option, that stores all possible options for fueling the car. There must be one-to-one mapping between a rental request and the fuel_option, since the customer is asked to select one at the time of booking the rental.

The Final Car Rental Data Model

In many areas, car rental companies are shifting toward using a keyless, self-service renting experience for their customers. They do not want to make their customers wait at a counter just to complete paperwork and pick up car keys. Can our current data model cater to such requirements? What changes are needed in our data model to make it happen?

Do you have any thoughts on our car rental data model? Let’s start a discussion! Feel free to share your input in the comments section.

You may also like

Car Rental Database Template Downloads

Car Rental Database Template Software

Car Rental Database Template Spreadsheet

Transportation has become a major need of every person in today’s world in past few years time. No matter where a person is going he needs a mode of transportation as walking is no option in this busy world. So, if one doesn’t have a car he hires it when needed. Hiring a car has become very easy now and so many people prefer this over traveling long distances in a public transport. Because of this reason, many rents a car businesses are flourishing today. By hiring a car on rent, one may feel independent and can roam around the city easily.

A car rental business needs to have a good system of recording all the payments of rent that is being paid by the customer. By carefully recording the rentals, it can be ensured that the entire due amount is paid in time and both the parties have a receipt as an evidence which can be used for future reference.

The car rental companies offer different kinds of cars and each car has different rates. These rates may vary because of type or condition of the car being offered or the demand for cars. Whatever may the reason be but as the rental companies trust the clients and giving their assets to them for a certain decided amount of time, they are advised to keep a close record of all the vehicles that they have rented out and the amount they have got in return for giving out the asset.

This record can have multi purposes like acting as a receipt of payments, record keeping and keeping a track of rentals. The best way to have a detailed account of these records is to make a car rental record sheet. This sheet will have the name of the rent a car service providing company and the name of the client along with his or her contact details. Apart from that, the description of the car being rented out must also be mentioned.

Car Rental Database

The typical record sheet consists of columns and rows in which the amounts are recorded. To make this sure that all the necessary information is recorded and the format is understood by all, a template of a car rental record sheet is commonly used.

Preview

Format: MS Excel