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
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.
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. 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
|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
|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.|
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
|DATE TIME||Period of rental.|
|rental_status||VARCHAR||Up to you to decide – “P”ending, “O”n rent, “R”eturned, “C”anceled, etc…|
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
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
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
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!