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
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
|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
|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
|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.|
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
|DATE TIME||Period of rental.|
|rental_status||VARCHAR||Up to you to decide – “P”ending, “O”n rent, “R”eturned, “C”anceled, etc…|
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
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
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
INFOGRAPHIC CHEAT SHEET
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!