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
We’ll present each subject area in the same order it is listed.
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.
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
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
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.
last_name– The user’s first and the last names.
city_id– A reference to the
citywhere the user is usually located.
current_city_id– A reference to the
citywhere the user is currently located.
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
role_id– The ID of the related
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
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_to– The time period when that property was active in our system. The
active_toattribute 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
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
property_id– References the
time_to– When this property was used to provide this service. The
time_toattribute 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
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
user_account_id– A reference to the
user_accounttable. 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
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_roletable) 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
servicerequired with that request.
city_id– A reference to the
citywhere 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”.
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 (
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
provides_id– A reference to the
providestable 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.
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_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.