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

 

 

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.

 

 

 

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 denied 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”.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart