Earn Money with Unused Stuff: A Sharing Economy Data Model

Emil Drkušić, Database designer and developer, financial analyst

by
Emil Drkušić
Database designer and developer, financial analyst

Posted: June 13, 2018

sharing economy, Airbnb, Uber

There isn’t much chance you’ve missed the whole idea of the sharing economy – whether you like it or not. Popularized by companies like Airbnb, Uber, Lyft, and many others, it lets people earn some cash by renting out their unused stuff. Let’s see the data model behind such an application.

Got a spare room? Sign up with Airbnb and make some extra money renting it out. Got a car and some free time? Become an Uber driver. And so it goes – the idea behind these companies and many more like them is almost the same. It’s all about sharing a resource with (mostly) strangers, with a perk for both parties. The owner gets money for their unused property, while the customer usually gets a good deal; this should be a win-win situation.

Of course, we need a platform to connect owners with customers and to keep track of important details. Today, we’ll present a data model that could manage the task. Settle back in your chair and enjoy the ride through the sharing economy data model.

What Do We Need in Our Data Model?

The idea of renting out property when we’re not using it seems very wise. First, the property gets used for its intended purpose; second, the rental will generate some kind of additional income. That could be cash, but could also be an exchange (e.g. someone in New York swapping apartments with someone in Paris for a week).

Cashless models are really cool and they usually depend on mutual understanding, goodwill, and honesty. However, this article will focus on sharing economy models that require payment. It’s not as romantic as cashless models, but the payment model is pretty effective.

We need a very simple way for a large number of property owners to reach a large number of interested customers and vice versa. This is the first requirement for our data model. We will have user accounts and at least two distinct roles – owner and customer.

The next thing we need is for our app to list all the available properties. For Airbnb, this would be apartments; for Uber, this would be cars. This article will focus more on renting apartments (an Airbnb-like data model) but I’ll keep the model general enough so it could easily be converted to any other desired sharing-economy service.

For each property owner, we’ll need to define the location where they operate. For apartments, this is pretty obvious (the city where the apartment is located). For transportation services, this will depend on the current location of the car and/or its owner.

For each property or resource, we’ll need to track usage periods and requests/reservations. This will allow us to find available properties when a new request is placed and to calculate occupancy rate and price. We’ll also be able to use other programs to analyze this data and produce other statistics.

The Data Model




The data model consists of five subject areas:

  • Countries & cities
  • Users & roles
  • Services & documents
  • Requests
  • Provided services

We’ll present each subject area in the same order it is listed.

Section 1: Countries and cities

Section 1: Countries and cities

We’ll start with the Countries & cities subject area. Although not specific to this data model, these tables are very important. Property-related services is usually geographically oriented. Our model is closely related to renting some kind of dwelling, so the physical location is crucial here. Of course, that location won’t usually change. There are some very special cases that could result in changing a property’s location, but I would treat that dwelling on its new location as a completely new property.

For car and/or driver apps like Uber, the current location of the car and driver is also very important. Unlike Airbnb-style apartment rentals, these property locations could change frequently.

The country table contains a list of UNIQUE names of the countries where we operate. The city table contains a list of all the cities where we operate. The UNIQUE combination for this table is the combination of postal_code, city_name, and country_id attributes.

Both of these tables could contain many additional attributes, but I’ve intentionally omitted them because they won’t add any value to this model.

Section 2: Users and Roles

Section 2: Users and Roles

The next thing we need to do is to define users and their behaviors or roles in our application. To do this, we’ll use the three tables in the Users & roles subject area.

A list of all users is in the user_account table. For each user, we’ll store the following details:

  • user_name – The UNIQUE name the user has chosen to access our application.
  • password – A hash value of the password chosen by the user.
  • first_name and last_name – The user’s first and the last names.
  • city_id – A reference to the city where the user is usually located.
  • current_city_id – A reference to the city where the user is currently located.
  • email – The user’s email address.
  • time_inserted – The timestamp when this record was inserted in the table.
  • confirmation_code – A code generated during the registration process to confirm the user’s email address.
  • time_confirmed – The timestamp when the email address was confirmed. This attribute contains a NULL value until the confirmation goes through.

The user will have different rights in the application according to their role. It’s also possible that one user can have more than one active role at the same time, e.g. they could be the owner of one property and the customer of another property. In that case, the user will use the same login details and will have the option to switch between roles. Each role will have its own screen in the app.

A list of all possible roles is stored in the role dictionary. Each role is UNIQUELY defined by its role_name. For simplicity’s sake, we can expect just two roles: “property owner” and “customer”.

A user could have the same role assigned multiple times during different periods. One such case would be if the user was renting their unused apartment and then decided not to rent their apartment because they needed it. However, after a few months, the same user decided to rent their apartment again. In this case, we’d deactivate their role and then activate it again.

A list of all roles that were ever assigned to users is stored in the has_role table. For each record in this table we’ll store:

  • user_account_id – The ID of the related user.
  • role_id – The ID of the related role.
  • time_from – The timestamp when this role was inserted in the system.
  • time_to – The timestamp when this role was deactivated. This will contain a NULL value as long as the role is still active.
  • is_active – Is set to False when the role is deactivated for any reason.

When inserting a new record in this table, we should check for overlapping records. This allows us to avoid making the same role valid twice during the same period.

Section 3: Services and Documents

Section 3: Services and Documents

The next thing we need to define are the services provided by users. We’ll also need to keep track of any related documents. To do that, we’ll need the tables in the Services & documents subject area.

Let’s start with the property table. Properties are whatever the objects of our service happen to be: dwellings, cars, bikes, etc. We can expect that users will take care of their own properties. For each property, we will need to define:

  • property_name – The screen name for that property, chosen by the user. This name is used when displaying the property to potential customers on the app. It should be brief and descriptive and set that property apart from other properties.
  • property_description – Additional textual description in unstructured format. We can expect a bunch of details here – basically everything from the size of the apartment to whether customers will get a welcome drink when they arrive. Welcome drinks in transportation services are much less likely to happen.
  • active_from and active_to – The time period when that property was active in our system. The active_to attribute will contain NULL value until the property is deactivated.
  • is_available – A flag denoting if this property is available at a specific time or not.
  • is_active – A flag denoting if that property is still active in our system. The value of this attribute will be set to False at the same moment active_to is set.

We’ll now move to the service dictionary. This is where we’ll define all possible services types, like “long-term rent”, “short-term rent”, “transport” etc. It contains the UNIQUE name of the service type and an additional description, if needed.

We’ll keep related properties, services, and users in the provides table. It will store periods when a property was available. In the case of transportation, this would tell us when a car and driver were actually working for our company. In the case of apartment rentals, it would tell us when a property was available. For each record here, we’ll have:

  • user_account_id – The ID of the user providing that service.
  • service_id – The ID of the service type provided.
  • property_id – References the property used.
  • time_from and time_to – When this property was used to provide this service. The time_to attribute will contain a NULL value until this record is deactivated.
  • is_active – Is set to False once this property won’t be used anymore or when this user will stop providing this service. This is set at the same moment when time_to is set.

The remaining two tables in this subject area are related to documents. (The user_account table is just a copy of the original, used here to avoid relations overlapping.) Our company will work with many property owners and there will be almost no chance to check everything in person. One way to ensure service quality is to have everything well documented.

The first table related to documents is the document_type table. This simple dictionary contains a list of UNIQUE type_name values. We can expect values like “property picture” and “owner ID” here.

A list of all documents is stored in the document table. These documents could be related to user accounts, properties, or both. For each document, we’ll store:

  • document_location – The full path to that document.
  • document_type_id – A reference to the document_type dictionary.
  • user_account_id – A reference to the user_account table. This attribute will only hold a value when the document is related to the user or if the document is related to the property but the user also owns that property.
  • property_id – A reference to the related property.
  • is_active – Denotes if this document is still active (valid) or not.

Section 4: Requests

Section 4: Requests

Before we can provide any service, we need to get some user requests. In apartment rentals, the customer will place his request for the desired property after they’ve searched the listings and found the dwelling they want. In the case of transportation services, requests are placed by customers via mobile application (e.g. they’re at the airport and need a ride in 20 minutes). We’ll talk about how we process requests in the next section; for now, let’s see how we manage them.

The central table in this subject area is the request table. For each request, we’ll store:

  • has_role_id – A reference to the user (and his current role, via the has_role table) who made that request.
  • request_status_id – A reference to the current status of that request.
  • status_time – The timestamp when that status was assigned.
  • service_id – The ID of the service required with that request.
  • city_id – A reference to the city where this service is required.
  • request_details – All additional request details, in unstructured textual format.
  • is_processed – A flag denoting if this request has been processed (i.e. assigned to the service provider).
  • is_active – This flag will be set to False only if a customer canceled his request or if the requested was canceled by the app for some reason.

A list of all possible statuses is stored in the request_status dictionary with status_name as the UNIQUE (and only) value. We can expect values like “request placed”, “property reserved”, “assigned to driver”, “drive in progress”, and “completed”.

The request_status_history table will store the history of all statuses related to requests. For each record in this table, we’ll store the ID of the related request (request_id), the status ID (request_status_id), the user account ID and the role the user had when they set that status (has_role_id). We’ll also record when each status was assigned (status_time).

Section 5: Provided Services

After the request is placed, we need to process it. A request will either be automatically assigned to the appropriate service provider (based on the requested service type, location, etc.) or it will be accepted manually by the service provider. We need just two more tables to handle this.

First is the provided_service table. For each record, we’ll include:

  • request_id – The ID of the related request.
  • provides_id – A reference to the provides table that denotes the service provider and the property included in this action.
  • details – All additional details, in structured textual format. This structure can include tags and values that describe request details. For a ride, this would mean the starting and ending point, the distance covered, etc.
  • start_time and end_time – The period during which this service was provided. Both these values will be set when the service has just started and ended.
  • grade_customer and grade_provider – Grades given by the customer and the service provider for that service.

The last table in our model is the invoice table. We’ll charge customers (customer_name) for the provided services (provided_service_id). For each invoice, we need to know the total_amount, any fees paid (fee_amount), when the invoice was issued (time_issued), and when it was paid (time_paid) The paid field serves as a flag indicating if an invoice has been paid.

What Do You Think About Our Sharing Economy Data Model?

Today we discussed a data model that could be used by a company like Airbnb or Uber. The backbone of such a business model are the customers and the service providers. There are a number of details I could add to this model. Still, I decided not to do that because the model would quickly get too large. Do you think I should have added something? If so, please tell me in the comments below.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Find out how to identify all the factors of a successful visualization: think like your audience, find the right chart type, and take out the meaning of the data. View course Discover our other courses: