Bike Rental System ERD (Simple Version)

Welcome to a quick sharing of an ERD of a bike rental system. Want to build your own bike rental system? Or got this as a school assignment? Well, here is my own simple version of the system, hope it helps to speed up your design process. Read on!

 

 

TABLE OF CONTENTS

 

 

BIKE RENTAL SYSTEM

To keep things simple, I have generalized the system into 3 groups of entities:

• Users – The elephant in the room. The customers, staff, and whoever else using the system.
• Bikes – Bicycles available for rental.
• Rental – Which bicycles are rented to which customers.

 

FOR REFERENCE ONLY

I can feel the “angry troll things” screaming “this design is stupid”, and “the diagram is inaccurate”. So here’s a quick disclaimer before we proceed – This ERD is a simple example. It is only for reference and it is highly subjective.

For example, some people may have multiple shops and locations. You will have to add that to your own system design. Also, some may have memberships, and offer different rates based on the membership level. For that, you will have to add a “rates” table.

Yep, it is impossible to build a “one size fits all” system here. You are building your own system, you have to decide your own rules.

 

 

 

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. Decide your own – “C”ustomer, “M”anager, “S”taff.

Some people love to create “multiple different types” of user tables – customers, staff, managers, etc… I don’t understand the logic behind that. Unless the datasets are very different, wouldn’t a single “user role” field do the trick?

 

PART 2) BICYCLES

2A) BIKES TABLE

COLUMN	DATA TYPE	NOTES
bike_id	VARCHAR	Primary key. Depending on the region, some bicycles may require a “lawful registration number”. But you can also assign any random unique ID.
bike_type	VARCHAR	You decide. Can be “R”oad, “Mountain”, “F”oldable, “E”lectric, etc…
bike_desc	VARCHAR	A short description of the vehicle.
bike_price	VARCHAR	Rental price. Up to you to decide – Could be the rental rates by the hour, or by the day.
bike_status	VARCHAR	Up to you to decide again. Maybe “A”vailable, “R”ented, “S”ervicing.

Feel free to add more fields as required – Maybe the maker, model, CC (for electric bikes), etc… As above, you may also want to have a separate “price” table, depending on the business model.

 

 

2B) BIKE HISTORY TABLE

COLUMN	DATA TYPE	NOTES
bike_id	VARCHAR	Primary Key.
history_time	DATE TIME	Primary Key.
history_status	VARCHAR	Up to you to decide. Maybe – “O”n rent, “R”eturned, “S”ervicing, etc…
history_notes	VARCHAR	A short and optional description of the “movement”. E.G. Bicycle serviced by XYZ.
user_id	INTEGER	Foreign key.
rental_id	INTEGER	Foreign key.

Take extra note that this table is called “bicycle history” and not “rental history”. Apart from the rentals, we can also use this table to track movements such as “sent for maintenance”, and “reserved for internal use”.

 

 

PART 3) RENTALS

3A) RENTAL TABLE

COLUMN	DATA TYPE	NOTES
rental_id	INTEGER	Primary Key.
bike_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.

This table should be self-explanatory – Customer X rented bicycle Y for period Z. A couple of notes here:

• Sharp ninjas should have noticed, we have “duplicate” copies of the customer’s name/email/tel here. But this is for a good reason, consider this:
  • Jane rented the bicycle today. Only the user ID is recorded in the rental table.
  • The next day, Jane changed her account name/email/tel and denies she rented the bicycle.
  • But thankfully, the rental records have an undeniable and unchanged user ID, name, email.
  • So yes, recording the name/email/tel at the time of rental is not “redundancy” by “integrity”.
• You will want to add another set of “billing name/email/tel” to the table – Some 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.

Finally, we have a table to store the total amounts of the rental. While we can calculate the total amount directly from the “date start” and “date end”, the “tax” and “discounts” may differ over time. So it’s best to keep records of these.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart