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

 

ER DIAGRAM

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

 

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.

 

 

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

 

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

Bike 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 *