Data Model Design: A Mobile App Marketplace for Local Services

Using a mobile app to hire builders, landscapers, and other skilled professionals makes taking care of property less of a hassle. In this article, we look at a data model to support such an app.

Is it easy for you to hire a plumber, handyman, or electrician? Most people would say no! That’s why there are several apps and services that match service professionals with buyers.

These apps are based on a simple, one-platform concept. On one side, this platform verifies professionals’ credentials, gives them a way to find customers, and collects buyers’ reviews and ratings when the job is done. On the other side, it allows buyers to find a provider, set up an appointment or reserve a time slot, and negotiate a price for work to be done. The object of this platform is to enable people to hire high-quality, trusted professionals that will work within a specific budget. And because it’s done via an app, it’s about as easy as it can be.

When I designed this data model, I was mostly thinking about building trades, like electricians, heating and cooling technicians, and so on. However, it could be used for just about any profession, including party planning, dog walking, and health coaches (to name just a few).

Let’s see how the data model for a local services mobile app would work.

The Data Model

The two main elements in this equation are the service providers and the customers. We won’t go into too much detail on how we store the customers’ data; that is fairly straightforward.

Providers can be trained in several areas and have several services to offer. For example, a provider may be trained in landscaping as well as roof cleaning. So we need more than just one area to store providers’ info; we need a place to map providers to all the services they offer.

We will also need an area that manages the entire service delivery process, from requests for quotes (i.e. a customer asks for offers on a project) to final feedback on the provider’s performance. Obviously, the better feedback that the provider gets, the more often they will get hired. Wouldn’t you rather hire a builder or repair tech will a lot of very good ratings?

You can see the full data model here. We’ll look at each of the subject areas individually:

• Providers
• Services
• Customers
• Service Delivery

Providers

This subject area stores information about the people who offer services on the platform.

The provider table captures basic details about service providers. The columns in this table are:

• id – A unique number assigned to each provider by the platform. Also the primary key of this table.
• first_name – The first name of the provider.
• last_name – The last name of the provider.
• email – The provider’s email address, used for email notifications.
• mobile_number – The primary contact method for each provider.
• is_individual – The platform will allow both individual professionals and companies to register. This column denotes if the provider is an individual professional or a company.
• is_registered_office – Whether a professional has a registered office. A registered office is not a prerequisite for providers to work with the platform, so this can be “Y” or “N”.
• office_address – The office address. Since this column will be applicable only when is_registered_office = ‘Y’, it is nullable.
• zip – The postal code of the provider’s office or residential address. It will play a key role when customers are searching for providers by location.

The provider_rating and provider_review_log tables manage the feedback mechanism for all providers.

The provider_review_log table captures feedback from customers who have used the platform to book a service. It uses a rating and review system to provide information about providers’ skills, professionalism, etc. (The written review is optional.) Feedback can only be given after the service is complete. Ratings are given on a scale of 1 (worst) to 10 (best). The columns in this table are:

• id – The primary key of this table.
• service_appointment_id – This column holds complete details about the customer who bought the service, when it was delivered, and the provider who delivered it, i.e. who the review is submitted for. It refers to the service_appointment table.
• punctuality_rating – Did the provider arrive on time?
• proficiency_rating – Did the provider do a good job?
• etiquettes_rating – Was the provider polite and professional in their behavior?
• communication_rating – How well did the provider communicate about the service?
• price_rating – Were the provider’s rates reasonable?
• overall_rating – The overall rating given to the provider.
• review – This optional column stores each customer’s comments about the provider. These reviews can be shown in providers’ profiles as testimonials.
• review_date – When the review was submitted.

The provider_rating table provides an overview of providers’ ratings (i.e. an average score for each rating area). The aggregated review data in this table will be refreshed as and when new ratings are entered. The columns in this table are:

• provider_id – References the provider table and relates the provider to the ratings.
• avg_punc_rating – The provider’s average rating for punctuality.
• avg_prof_rating – The provider’s average rating for proficiency.
• avg_eti_rating – The provider’s average rating for etiquette/professionalism.
• avg_comm_rating – The provider’s average rating for communication.
• avg_price_rating – The provider’s average rating for price.
• avg_overall_rating – The provider’s average overall rating.
• last_updated_on – The most recent date when these records were updated.

Services

This subject area maps providers with all the services they offer. Remember, one provider might be skilled in several areas.

The service and service_category tables list all possible services and categories. Their structure is very similar: just an id column and a description of the service or service category. Notice that the service table also has a referential column to identify the category related to an individual service. We won’t go into these columns in detail; they are pretty self-explanatory. Instead, look at some examples of service categories and individual services:

• Party and Event Services –
• Photographer
• Event Management
• Bridal Makeup Artist
• Party Caterer
• Party Cake
• DJ
• Musician
• Home Services –
• Air Conditioning or Furnace Repair
• Plumbing
• Electrician
• Carpenter
• Health and Wellness Services –
• Beauty Services
• Personal Trainer
• Yoga Instructor

The service_provider_map table connects providers with the services they offer. The columns in this table are:

• id – The primary key of this table.
• service_id – The service that provider is offering.
• provider_id – The provider offering the service.
• billing_rate_per_hour – How much money providers are expecting per hour for the service.
• experience_in_months –How experienced a provider is in delivering this service. A provider might have been doing home repairs for two years, but has only just gone into landscaping; therefore, we have this column to show providers’ experience in every skill they list.
• service_offering_description – A brief description of the service offered. It is optional and nullable.

Customers

This subject area explains the other side of the app – the customers, or the people shopping for services.

The customer table holds basic details about customers. The columns in this table are:

• id – The primary key of the table.
• first_name – The customer’s first name.
• last_name – The customer’s last name.
• email – The customer’s email address.
• mobile_number – The customer’s mobile number.

Customers can request services for their home, office, or any other place. Therefore we have created a separate address table to hold address details about the location involved in the service. All its columns are self-explanatory.

Service Delivery

As soon as a service is requested, a new record is inserted into the service_request table. Providers then view this request and submit their offers. These offers are emailed to customers and/or viewed in the app itself. Customers may choose zero, one, or more than one offer and schedule an appointment with the providers.

Let’s look at the service_request in more detail. Its columns are:

• id – The primary key of this table.
• customer_id – The customer that is requesting the service.
• address_id – The location where the service will be performed.
• service_id – The service requested (i.e. fixing a leaky roof).
• requirement_desc – Describes the requested service, i.e. what the project involves and what the customer expects to be done.
• service_require_on – The date when the service should be performed.
• expected_start_time – When the provider is expected to start the service.
• tentative_efforts_required_in_hours – How long the service is expected to take. This is an optional column, so I have kept it nullable.

The service_delivery_offer table holds all offers made in response to requests. It comes into the picture after providers submit offers to a request by a customer.

• id – The primary key of this table.
• service_request_id – Identifies the customer and the project related to the request.
• service_provider_map_id – The provider who is submitting the offer.
• discount_in_percentage –The discount, if any, the provider will give on their base price.
• estimated_cost –An estimate of the cost for the service, submitted by the provider. This column is optional, so this field is nullable.
• offer_submit_date – The date when an offer is submitted by the provider. This column helps sort offers on a first-come-first-serve basis.
• is_offer_accepted – This column is NULL by default. It will only hold a “Y” if a customer accepts a provider’s offer and only hold an “N” if the customer explicitly rejects an offer.

The service_appointment table stores service appointments. It comes into the picture once a service offer is accepted by the customer. The columns in this table are:

• id – The primary key of this table.
• service_delivery_offer_id –Which offer has been accepted.
• service_deliver_on –When the service is to be delivered.
• service_start_time – When the service should start. Initially, this will store a tentative time, but it will be updated to the actual start time once the service is underway.
• service_end_time – This will have no value until the service is actually completed. Then it will be updated.

Both of these columns (service_start_time and service_end_time) are used in the final billing calculation.

What Would You Add to This Data Model?

We have left some functions out of this data model. For example, we could integrate a payment gateway into our app and charge a small percentage of each project cost as a fee. How would we support this in the data model? What else would you add? Let us know in the comments section!