Creating a Data Model for Carpooling

Nowadays, carpooling is accepted and promoted by people around the globe. It certainly reduces one’s personal carbon footprint, and it can be more cost-effective than renting or buying a car.

Carpooling also takes a lot of work – organizational work that can readily be done by a well-designed database. This article explains a detailed data model that a carpooling website could use.

Data Design, Meet Carpooling

So, we need to design a data model for a ride-share (aka carpooling) website.

Carpooling is a bit different than a car rental. In carpooling, the car is owned by one person, and they offer rides to others. Any co-travelers pay towards the ride cost, including fuel, road tolls, etc.

Project Requirements:

• The website should allow the users (aka ride-share members) to register themselves using their name, phone number, email address, driver’s license number, etc.
• Members should be allowed to set their preferences regarding rides and co-travelers.
• Members called ride owners can create a ride by inputting their trip details (i.e., start and destination points, start time, per-rider costs, etc.)
• Other members can search for available rides to a destination city.
• Members who are looking for a ride can contact the ride owner and put in a reservation for their seats.
• The ride owner should be able to approve or reject reservation requests.
• Based on the action taken by the ride owner on the reservation request, the count of available seats should be updated.
• The ride owner can also mark en-route cities, which are cities on the way from their starting point to their destination. If they wish, the ride owners should also be able to accommodate people for en-route cities.

With these parameters in mind, let’s identify the main entities and relationships for our carpooling data model.

Identifying Entities and Relationships

When I see the requirements as a whole, I can easily figure out the main entities. They are:

• members (including rider owners and co-travelers)
• car
• preferences
• ride
• city
• ride request

Member – Everyone who visits this website has to register before using it. In this process, they need to provide details like first_name, last_name, gender, email, and contact number. For co-travelers, these items are sufficient. Ride owners, who will presumably be driving, need to fill in some additional details like driving_license_number and driving_license_valid_from should also be included. The license information tells co-travelers something about the driving experience of the ride owner. This will help the co-travelers to select the best ride available. I’ll add one table named member with all required columns.

Car – The ride owner needs to add details for at least one car before creating a ride. So let’s create another table, called car, to store this information. One member can own more than one car. A ride can depend on a member-car pair, so we need another table to establish this relationship. We’ll call this table member_car. I will refer primary key of this table in my ride table where I will store ride details. I’ll also add one column, namely comfort_level, that stores the comfort level of a car on a scale from 0 to 5. This level is automatically calculated by the system, based on the other member-provided details about the car.

Preferences – Preferences matter to everyone. The website allows members to fill in their preferences about the car and their co-travelers. These details remain optional at the time of registration, but must be filled before creating a ride. The ride owner will likely look for people with similar preferences so that everyone travels comfortably. People who are searching for rides do the same.

Some basic preferences for car travel are:

• Is smoking allowed inside the car?
• Are pets allowed along?
• How talkative is the ride owner? What level of chatter is acceptable during the ride? (Possible responses here include none, light chitchat, gabfest.)
• What type of music does the ride owner like?
• What music volume does the ride owner allow?

Since these details are optional during registration, I’ll create another table named member_preference to store these details. Two additional tables store possible options for music (music_preference) and in-car conversation (chitchat_preference).

Let’s have a zero-to-one relationship between the member and member_preference tables, as members may or may not set their preferences into the system when they sign up, and there is just one record for preferences per member.

City – One master table, city, is needed to store a list of all the cities served by the website. It should include the relevant state and country information for each city.

Ride – A member can create a ride by filling which car he is traveling by; which city he is starting from; which city he is heading towards; the date and time of the journey; the number of seats available; and contribution per head. The contribution per head is the amount that each co-traveler has to pay towards ride expenses. The ride owner can also mention how much luggage he is expecting from co-travelers so that everything will fit in the car. So we add one column, luggage_size_allowed, for this item. Possible values for this column would be light, medium and heavy.

Ride Request – Members can look at the list of available rides from one city to another or put in a request for a specific trip. We definitely need one table to store details about such requests. A table called request fits the purpose. The request is initially entered as a ‘submitted’ request, and the ride owner is the only person who is allowed to approve or reject it. The number of available seats in the ride table will be adjusted for each approval and/or rejection.

En-route Cities – Ride sharing is not all about going straight from starting point to destination. One can share a ride with others for en-route cities as well. For example, if a man is traveling from Chicago to Miami, he can accommodate someone who wants to go from Chicago to Nashville. Nashville is one of the cities he’ll pass in his route, so it is an en-route city. Our system should allow ride owners to set en-route cities based on the route they are going to follow to reach their destination. If co-travelers want, they can get off at any of the en-route cities; their travel costs will be pro-rated accordingly.

We’ll create another table called enroute_city for this purpose. Records will be added when the ride owner adds en-route cities to their ride. Members can then request reservations to travel to one of the en-route cities. Therefore, I refer the primary key of this table into the request table.

The order_from_source column in enroute_city table signifies the course the ride owner is going to follow for the journey.

Reports On The Website:

There are various reports (data extracts) that can be shown on this website. Let me explain a few of them:

1. Rides available from one specific city to another – This is one of the reports that will be extracted quite frequently, as it depicts the gist of this website. Most of the members will access this website in order to look for travel alternatives or ride shares. When extracting this report, members need to enter their starting and destination city names. The SQL follows:

   Select m.first_name || ‘ ‘ || m.last_name as “Ride Owner”, c.name as “Car”, c.comfort_level as “Comfort Level”, mp.is_smoking_allowed, mp.is_pet_allowed, r.travel_start_time, r.contribution_per_head, seats_offered 
   from ride r, member_car mc, car c, member m, member_preference mp
   where r.member_car_id = mc.id and mc.member_id = u.id 
   and mc.car_id = c.id and m.id = mp.member_id
   and source_city_id = (select city_id from city where city_name = ‘CHICAGO’)
   and destination_city_id = (select city_id from city where city_name = ‘MIAMI’)
   and seats_offered > (select count(id) from request req, request_status reqs where req.request_status_id = reqs.id and upper(reqs.description) = ‘APPROVE’ and req.ride_id = r.id);
   

2. List of submitted / approved requests for a ride – This report will be displayed to the ride owner. It will show who has submitted the ride request, and the owner can take action on requests from this report only. The SQL for this follows:

   select first_name || ‘ ‘ || last_name as “Submitter”,  req.created_on as “Submitted on”, rs.description as “Request Status” 
   from member m, request req, request_status rs
   where m.id = req.requester_id and rs.id = req.request_status_id
   and req.ride_id = ;
   

3. Previous and current rides offered – These reports will be displayed to ride owners on their own dashboards. The following SQL can be used to generate a list of rides the ride owner currently offers:

   Select m.first_name || ‘ ‘ || m.last_name as “Ride Owner”, c.name as “Car”, c.comfort_level as “Comfort Level”, mp.is_smoking_allowed, mp.is_pet_allowed, r.travel_start_time, r.contribution_per_head, decode(seats_offered,0,’FULL’, seats_offered || ‘ seats available‘) from ride r, member_car mc, car c, member m, member_preference mp
   where r.member_car_id = mc.id and mc.member_id = m.id 
   and mc.car_id = c.id and u.id = mp.member_id
   and r.travel_start_time >= sysdate
   and m.id = ;
   

   And this SQL can be used to extract a list of rides previously offered:

   Select m.first_name || ‘ ‘ || m.last_name as “Ride Owner”, c.name as “Car”, c.comfort_level as “Comfort Level”, mp.is_smoking_allowed, mp.is_pet_allowed, r.travel_start_time, r.contribution_per_head, decode(seats_offered,0,’FULL’, seats_offered || ‘ seats available‘) from ride r, member_car mc, car c, member m, member_preference mp
   where r.member_car_id = mc.id and mc.member_id = m.id 
   and mc.car_id = c.id and u.id = mp.member_id
   and r.travel_start_time < sysdate
   and m.id = ;
   

4. List of co-travelers for a ride – This report will be available to all co-travelers, including the ride owner. All of them can generate a list of all co-travelers for any of their past or future rides.

   select first_name || ‘ ‘ || last_name 
   from member m, request req, request_status rs
   where m.id = req.requester_id and rs.id = req.request_status_id
   and rs.description = ‘APPROVED’
   and req.ride_id = 
   UNION
   select first_name || ‘ ‘ || last_name 
   from member m, member_car mc, ride r
   where m.id = mc.member_id and mc.id = r.member_car_id 
   and r.id = ;
   

The Final Data Model

What About Improvements?

Can we further improve this model? Yes, we can! There are still some areas which need to be taken care of.

What if one wants to create recurring ride requests? Suppose a driver travels from one city to another every weekend, and they are always willing to share this ride. A recurring request would be more convenient.

How can a person rely on some unknown guy who is offering a ride? There should be some way to help people evaluate others before requesting a ride. One viable mechanism is to publish and share feedback about ride owners and co-travelers. These details will surely help others to book a ride with strangers more confidently. For this to happen, what changes are required to our data model?

Feel free to share your inputs on this model.