Data Base Design Tool Rental Examples

Renting goods and services is very popular today. Services like Airbnb rubbed the renting lamp and let the genie out, especially for travelers. They've opened up new horizons, and in the future we can expect that all kinds of rental services will become even more common.

In this article, we'll describe a database model that could be used to run an application for renting apartments, rooms, and anything else you can think of.

First, let's consider a few facts about renting. This information will inform how we build the model.

Some Basic Facts About Renting

Here are a few obvious (and not so obvious) questions about renting that we should answer.

What can be rented?

Think of Airbnb. Your first association is probably related to traveling somewhere and needing a place to stay. You use their website to search for a room or apartment, using criteria like location, price, and guests' feedback. However, rentals are by no means limited to accommodations. You don't even need to travel. Some other things you could rent include cars, musical instruments and equipment, sports equipment, or even a tour guide.
Why is renting so popular?

Imagine that you bought yourself a banjo and decided to become a banjo superstar. It's been a few years and hasn't worked out as expected. You still love to occasionally play your banjo, but most of the time it sits and collects dust. Now imagine somebody needs a banjo for a few days. It's a simple win-win situation. If that someone rents your banjo, they'll pay way less than if they bought themselves one. And you'll earn something from your rusty banjo sitting unused in a corner.
What should the renting app look like?

An application should always be as simple as possible, but here we can make an exception. If somebody is willing to pay to rent an item, they'll probably take some time to find what suits them. So this application could focus primarily on functionalities and not strictly on simplicity. Of course, we'll need to utilize a web service that performs well on computer screens and mobile devices.
Who are involved in the rental process?

The rental process usually involves two parties: the person who owns the item, and the person who rents it. The owner is also known as the rentor or rentier (which is what we will call them in this article). The renter is the person who rents the item. (Of course, rental agencies can be involved too, but for the sake of this model we'll assume they aren't.)

The Data Model

The data model consists of three main subject areas:

Items
Leased items and grades
Locations

There is also a single table that is outside of any area, the user_account table. It is "used" twice in our model to avoid references overlapping.

I'll start with the user_account table first because it contains list of all renters and rentiers. After that, I'll describe the Items subject area, where we'll store records for all the rented items. Then, we'll relate rentiers, renters, and items using the tables in the Leased items and grades subject area.

The User Account Table

User Account table

We'll list all our app's users in the user_account table. Each user can be a rentier, a renter, or both. The app's interface will present both options and we'll treat the user's ID as a rentier ID or renter ID according to which option the user chooses.

All attributes in this table are mandatory. The username and the email attributes can contain only UNIQUE values. For each user, we'll store a location using predefined location_id as well as a specific location_details like their address.

The phone, mobile and email attributes store users' contact information. As we already mentioned, email is mandatory but we can expect that users will also fill in their phone numbers.The last attribute, registration_time, stores when the user created their app profile.

Section 1: Items

Items subject area

In this section, we'll store all rental items and define their type, characteristics, and price.

The most important table in this section is the item table. Records for all items that are or were ever offered for rent are stored here. We'll store as many details we need to describe the item to the renter. The attributes in this table are:

item_name – The name given to an item by the rentier.
item_type_id – References the item_type dictionary.
location_id and item_location – Describes where the item currently is; the first one uses data from a dictionary table and the second one gives a more exact description.
description – The rentier's description of the item. Since we expect many different item types, we'll store this description as a TEXT data type.
owner_id – References the user_account table and denotes the owner (rentier) of the item.
price_per_unit – How much the rentier expects to get paid for renting out the item (e.g. this would be set to "15" if the rentier expects 15 EUR per day).
unit_id – The unit of measurement used to define the rental period (e.g. per day, per hour).
available – If an item is currently available. An item is not available if it's being rented or if the rentier sets its status to unavailable (e.g. the rentier doesn't want to rent out a room anymore or the bike they were renting out broke). In all other cases, items are available for rent.

The other two tables in this section are dictionaries. The item_type dictionary holds all possible item categories, such as rooms, apartments, bikes, guitars, etc. The unit dictionary stores values that define the rental period – per hour, per day, fixed price, etc. Besides their primary key attribute ("id"), both dictionaries contain only one other value: their name attribute. This attribute is also the UNIQUE key in these tables.

Section 2: Leased Items and Grades

This section is a true heart of our model. The grade_category table is a dictionary, while two other tables in this section contain values that are important to the renter. I'll explain as we go.

Leased items and grades subject area

The most complex table in the entire model is the item_leased table. Let's take a look at its attributes:

item_id – References the item table.
renter_id – References the "user" table and denotes which user rented the item.
time_from and time_to – The time period the renter has rented the item. We can use this information to determine when the item is available again and to calculate the rental price.
unit_id – References the "unit" dictionary.
price_per_unit – The price per single unit.
discount – The discount amount, if any.
fee – The fee amount. We can expect that the web service owner will charge a fee, such as a percentage of the rental earnings or a flat fee per transaction.
price_total – The final price charged to the renter; it's calculated from "number of units" (defined by "time_from", "time_to" and "unit_id") * "price_per_unit" – "discount" + "fee".
rentier_grade_description – The renter's remarks, if any, about their experience with the item's rentier.
renter_grade_description – The rentier's remarks, if any, about their experience with the renter.

The last two attributes in this table, rentier_grade_description and renter_grade_description, can contain NULL values. We can expect that grades will be assigned once the rental has expired. There is also a possibility that neither person will give a grade. In that case, both attributes will contain NULL values.

All grades related to a single rental instance are stored by category in the grade table. The attributes in this table are:

item_leased_id – References the item_leased table
grade_category_id – References the grade_category dictionary.
user_from – The user ID of the person who gave that grade.
user_to – The user ID of the person who was graded.
grade – The numerical representation of the grade. It is a rentiers' grade if grade."

user_from = item_leased."

renter_id and renters' grade if grade."

user_from = item."

owner_id.
description – A grade description related to that category only.

The last table in this section is the grade_category dictionary. For each category, we'll enter the category_name. Notice that this attribute alone does NOT contain UNIQUE values: we could have the same categories for different item types. The table's last attribute, who_grades, denotes who is able to grade that category for that item type, either the renter (e.g. value 1) or rentier (e.g. value 2).Therefore, the category_name – item_type_id – who_grades combination forms the UNIQUE key of the table.

Section 3: Locations

Locations subject area

This section is used to store information where our users and items are located. It's not specific to this model, so I'll only give it a quick explanation.

The location dictionary contains list of towns defined by their postal_code, name, description and a related country_id.

The final table in our model is the country table. It contains list of UNIQUE country names.

How Would You Improve This Model?

The model we discussed in this article should be flexible enough to cover renting any item type, but there are many improvements we could make. Some things that come to my mind are storing messages during the negotiation phase; creating and canceling rental reservations; including item insurance, and adding account and payment details.

Can you think of a few more improvements? What would you add? Let us know, and also feel free to share your experiences using similar applications.