Car Rental System ERD (Simple Version)

Welcome to a quick sharing of an ERD of a car rental system. Need to build an online portal for a car rental service? Well, here is my own simple version of the system, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

CAR RENTAL SYSTEM

Let us generalize the system into 3 groups of entities:

  • Users – To address the elephant in the room. The customers, drivers, staff, and whoever uses the system.
  • Cars – Vehicles available for rental.
  • Rental – Which vehicles are rented to which customers.

 

FOR REFERENCE ONLY

My developer senses are tingling, I can hear the trolls screaming “the diagram is not accurate” and “this is stupid”. Here’s a quick disclaimer before we go any further – This ERD is only for reference and it is highly subjective.

For example – Some people may have multiple stores and want to add that to the system, or even offer driver services (add another user-to-cars table). Yep, you are building your own system. So feel free to modify the parts that don’t make sense.

 

 

TABLE DETAILS

Now that we are done with the overview, let us get into more details on the entities or tables.

 

PART 1) USERS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary key.
user_name VARCHAR User’s full name.
user_email VARCHAR Unique, user’s email.
user_tel VARCHAR User’s telephone number.
user_password VARCHAR Password. Encrypt in your own system.
user_role VARCHAR User’s role. Invent your own – “C”ustomer, “M”anager, “S”taff, “D”river.

Some people prefer multiple tables for different roles. I find that kind of “not intelligent”… Why create so many “different types” of user tables, when all of them have the same fields? Wouldn’t a single user_role field do the trick?

 

PART 2) CARS

2A) CARS TABLE

COLUMN DATA TYPE NOTES
car_id VARCHAR Primary key. Car plate number.
car_type VARCHAR Invent your own. Could be “S”edan, “C”oupe, “M”PV, “L”imousine, etc…
car_desc VARCHAR A short description of the vehicle.
car_price VARCHAR Rental price. Up to you to decide – Could be the rental rates by the hour, or by the day.
car_status VARCHAR Up to you to decide, a good place to start is  – “A”vailable, “R”ented, “S”ervicing.

Feel free to add more fields as required – Maybe auto/manual, car model, car maker, etc… Also, you may want to have a separate “car price” table, depending on the business model. E.G. Offer “rent by the hour”, “rent by day”, and various packages.

 

 

2B) CAR HISTORY TABLE

COLUMN DATA TYPE NOTES
car_id VARCHAR Primary Key.
history_time DATE TIME Primary Key.
history_status VARCHAR Up to you to decide. Maybe – “I”n, “Out”, “S”ervicing.
history_notes VARCHAR A short and optional description of the “car movement”. E.G. Out – Customer picked vehicle from XYZ.
user_id INTEGER Foreign key.
rental_id INTEGER Foreign key.

Now, take note that this table is called “car history” and not “rental history”. This table is used to track both rentals and movements within the company. E.G. Sending the vehicle out for maintenance, or “for internal use of company trip”.

 

PART 3) RENTALS

3A) RENTAL TABLE

COLUMN DATA TYPE NOTES
rental_id INTEGER Primary Key.
car_id VARCHAR Foreign Key.
rental_start

rental_end

DATE TIME Period of rental.
rental_status VARCHAR Up to you to decide – “P”ending, “O”n rent, “R”eturned, “C”anceled, etc…
user_id INTEGER Foreign Key.
user_name VARCHAR Customer’s name.
user_email VARCHAR Customer’s email.
user_tel VARCHAR Customer’s telephone.

Now, this table should be pretty self-explanatory – Customer X rented vehicle Y for period Z. Just a couple of notes here:

  • Why are we “duplicating” the customer’s name/email/tel. Consider this:
    • John rented the vehicle today. Only the user ID is recorded in the rental table.
    • The next day, John changed his account name/email/tel and denies he rented the vehicle.
    • So yes, recording the name/email/tel at the time of rental is not “redundancy” by “integrity”.
  • You may want to add another set of “billing name/email/tel” to the table – Customers may pay with their company’s credit card.

 

 

3B) RENTAL TOTALS TABLE

COLUMN DATA TYPE NOTES
total_id INTEGER Primary key.
rental_id INTEGER Foreign key.
total_name VARCHAR Total name/title.
total_price FLOAT Amount.

Lastly, we have a table to store the rental total amounts. I know, it is possible to calculate the total amount directly from the “date start” and “date end”. But we still need a separate table to store entries such as “tax” and “discounts”.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

 

INFOGRAPHIC CHEAT SHEET

Car Rental System ERD (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *