A database schema for an InDrive-clone ride hailing app that helps link riders to drivers on frequently-travelled transport routes (pricing based on a fair bargain) based on OpenStreet Maps info using either MySQL, SQLite or PostgreSQL as primary database

low_budget_ride_hailing_app_design_database.sql

-- MySQL v8.0.16
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci; -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;

CREATE TABLE rider_details (
    id bigint NOT NULL,
    first_name varchar(90) NOT NULL,
    last_name varchar(90) NOT NULL,
    email varchar(50) NOT NULL UNIQUE,
    profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
    gender enum('male', 'female') NOT NULL,
    date_of_birth date NOT NULL,
    registered_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT riderunq UNIQUE (id, first_name, last_name),
    PRIMARY KEY (id)
); 

CREATE TABLE driver_details (
    id bigint NOT NULL,
    first_name varchar(90) NOT NULL,
    last_name varchar(90) NOT NULL,
    email varchar(50) NOT NULL UNIQUE,
    insurance_policy_number varchar(10) NOT NULL,
    insurance_policy_broker varchar(40) NOT NULL,
    profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
    gender enum('male', 'female') NOT NULL,
    date_of_birth date NOT NULL,
    years_of_experience char(2) NOT NULL,
    CONSTRAINT driverunq UNIQUE (id, first_name, last_name, date_of_birth),
    PRIMARY KEY (id)
);

CREATE TABLE user_accounts (
    id bigint NOT NULL,
    email varchar(50) NOT NULL UNIQUE, -- data redundancy for easier access
    full_name varchar(150) NOT NULL, -- data redundancy for easier access
    password varchar(36) NOT NULL,
    last_login_at timestamp,
    profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
    email_verified tinyint(1) NOT NULL DEFAULT 0,
    account_type enum('driver', 'rider') NOT NULL,
    rider_id bigint UNIQUE NULL, -- if `account_type` is 'driver' then this column is NULL else it contains a valid rider id
    driver_id bigint UNIQUE NULL, -- if `account_type` is 'rider' then this column is NULL else it contains a valid rider id
    PRIMARY KEY(id),
    FOREIGN KEY(rider_id) REFERENCES rider_details(id) ON DELETE CASCADE,
    FOREIGN KEY(driver_id) REFERENCES driver_details(id) ON DELETE CASCADE
);

ALTER TABLE user_accounts ADD CONSTRAINT chk_accounttype_driver_rider_null_on_rows CHECK (account_type = 'driver' AND rider_id IS NULL AND driver_id IS NOT NULL);

ALTER TABLE user_accounts ADD CONSTRAINT chk_accounttype_rider_driver_null_on_rows CHECK (account_type = 'rider' AND driver_id IS NULL AND rider_id IS NOT NULL);

CREATE TABLE vehicles (
    id bigint NOT NULL,
    vehicle_type enum('car', 'suv-jeep') NOT NULL,
    owner_id bigint NOT NULL,
    maker_specs enum('toyota-camry', 'toyota-corolla', 'toyota-4runner', 'honda-city', 'lexus-es350', 'nissan-patrol', 'honda-accord', 'peugeot-406', 'honda-civic', 'peugeot-302',  'audi-', 'benz-C-class', 'benz-S-class', 'bmw', 'toyota-siena') NOT NULL,
    engine_specs enum('v6-single-transmission_4cyl', 'v8-double-transmission_6cyl') NOT NULL,
    fueltank_specs enum('100l-guage', '120l-guage') NOT NULL,
    plate_number varchar(10) NOT NULL,
    color enum('red', 'black', 'white', 'green', 'cream', 'gray', 'blue') NOT NULL,
    model_year char('4'),
    particulars_status json NOT NULL DEFAULT (JSON_OBJECT()), -- e.g. '{ "valid_driver_license": true, "valid_vio-inspection_kits": false }'
    enrolled_at timstamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    FOREIGN KEY(owner_id) REFERENCES driver_details(id) ON DELETE CASCADE
);

CREATE TABLE rides (
    id bigint NOT NULL,
    vehicle_id bigint NOT NULL,
    status_started tinyint(1) NOT NULL DEFAULT 0,
    status_completed tinyint(1) NOT NULL DEFAULT 0,
    status_cancelled tinyint(1) NOT NULL DEFAULT 0,
    departure_time DATETIME,
    arrival_time DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(vehicle_id) REFERENCES rider_vehicles(id) ON DELETE CASCADE
);

ALTER TABLE rides ADD CONSTRAINT chk_ride_req_stats_startd CHECK (status_cancelled <> 1 AND (status_started = 1 OR status_started = 0));

ALTER TABLE rides ADD CONSTRAINT chk_ride_req_stats_cancld CHECK (status_completed <> 1 AND (status_cancelled = 0 OR status_cancelled = 1));

ALTER TABLE rides ADD CONSTRAINT chk_ride_req_stats_compltd CHECK (status_started = 1 AND (status_completed = 0 OR status_completed = 1));


CREATE TABLE booking_requests (
    id bigint NOT NULL,
    booker_id bigint NOT NULL,
    status_cancelled tinyint(1) NOT NULL DEFAULT 0,
    status_confirmed tinyint(1) NOT NULL DEFAULT 0,
    status_executed tinyint(1) NOT NULL DEFAULT 0,
    payment_type enum('cash', 'card', 'bank-transfer') NOT NULL,
    booked_on DATETIME NOT NULL,
    has_multiple_stops tinyint(1) NOT NULL DEFAULT 0,
    payment_currency enum('NGN-kobo', 'USD-cents', 'KSH-cents') NOT NULL,
    estimated_base_fare bigint NOT NULL DEFAULT 0,
    surge_multiplier decimal(1, 1) NOT NULL DEFAULT 0,
    route_info json NOT NULL DEFAULT(JSON_OBJECT()), -- ('{ "pickup_location_latlng": null, "dropoff_location_latlng": null, "ETA": "0-seconds", "total_distance_travelled": "0-metres", "departure_time": null, "arrival_time": null, "traffic_conditions": { "status": "conjested" }, "intermediate_stops": [{ lat_lng }, {lat_lng}] }')
    PRIMARY KEY(id),
    CONSTRAINT idbookedonunq UNIQUE(id, booker_id, booked_on),
    FOREIGN KEY(booker_id) REFERENCES rider_details(id) ON DELETE CASCADE
);

ALTER TABLE booking_requests ADD CONSTRAINT chk_bkng_req_stats_cancld CHECK (status_confirmed <> 1 AND (status_cancelled = 0 OR status_cancelled = 1));

ALTER TABLE booking_requests ADD CONSTRAINT chk_bkng_req_stats_confmd CHECK (status_cancelled <> 1 AND (status_confirmed = 0 OR status_confirmed = 1));

ALTER TABLE booking_requests ADD CONSTRAINT chk_bkng_req_stats_exectd CHECK (status_confirmed = 1 AND (status_executed = 0 OR status_executed = 1));

CREATE TABLE ride_bookings (
    booking_id bigint NOT NULL,
    ride_id bigint NOT NULL,
    driver_id bigint NOT NULL,
    rider_and_payer_id bigint NOT NULL,
    amount_paid bigint,
    payment_currency enum('NGN-kobo', 'USD-cents', 'KSH-cents') NOT NULL,
    status enum('assigned', 'en_route-to-arrival', 'en_route-to-destination', 'ended') NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(booking_id, ride_id),
    CONSTRAINT bookingsunq UNIQUE (booking_id),
    CONSTRAINT ridesunq UNIQUE (ride_id),
    FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY(ride_id) REFERENCES rides(id) ON DELETE CASCADE,
    FOREIGN KEY(driver_id) REFERENCES driver_detailsn(id) ON DELETE CASCADE,
    FOREIGN KEY(rider_and_payer_id) REFERENCES rider_details (booker_id) ON DELETE CASCADE
);

-- This table is populated after the fact (i.e. after the peer-to-peer exchange)
CREATE TABLE cached_ride_booking_convo_thread_messages (
    id bigint NOT NULL,
    booking_id bigint NOT NULL,
    ride_id bigint NOT NULL,
    parent_id bigint,
    author_id bigint NOT NULL,
    content text,
    created_at datetime NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(parent_id) REFERENCES ride_booking_convo_thread_messages (id) ON DELETE NO ACTION,
    FOREIGN KEY(author_id) REFERENCES user_accounts (id) ON DELETE CASCADE,
    -- @SEE: 👇🏾 https://stackoverflow.com/a/10566463
    FOREIGN KEY (booking_id, ride_id) REFERENCES ride_bookings (booking_id, ride_id) ON DELETE CASCADE
);

CREATE TABLE ride_reviews (
    id bigint NOT NULL,
    ride_id bigint NOT NULL,
    reviewer_id bigint NOT NULL.
    rating enum('1-star', '2-star', '3-star', '4-star', '5-star') NOT NULL,
    comment text,
    PRIMARY KEY(id),
    FOREIGN KEY(reviewer_id) REFERENCES rider_details(id) ON DELETE NO ACTION,
    FOREIGN KEY(ride_id) REFERENCES rides(id) ON DELETE CASCADE,
);

CREATE TABLE ride_routes (
    id bigint NOT NULL,
    ride_id bigint NOT NULL,
    driver_id bigint NOT NULL,
    rider_id bigint NOT NULL,
    country varchar(20) NOT NULL,
    origin_latlng point NOT NULL,
    destination_latlng point NOT NULL,
    PRIMARY KEY (id),
    SPATIAL INDEX `SPATIAL` (origin_latlng),
    SPATIAL INDEX `SPATIAL` (destination_latlng)
    FOREIGN KEY (ride_id) REFERENCES rides (id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES driver_details (id) ON DELETE CASCADE,
    FOREIGN KEY (rider_id) REFERENCES rider_details (id) ON DELETE CASCADE,
);

CREATE TABLE ride_routes_telemetry (
   id bigint NOT NULL,
   ride_id bigint NOT NULL UNIQUE,
   speed_kmh decimal(5, 2),
   altitude_m decimal(7, 2),
   avg_speed_kmh decimal(5, 2),
   max_speed_kmh decimal(5, 2),
   cadence_rpm int,
   PRIMARY KEY (id),
   FOREIGN KEY (ride_id) REFERENCES rides (id) ON DELETE CASCADE
);

CREATE TABLE ride_routes_telemetry_metrics (
   id bigint NOT NULL,
   telemetry_id bigint NOT NULL,
   metric_key varchar(40) NOT NULL,
   metric_value decimal(8, 2) NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (telemetry_id) REFERENCES ride_routes_telemetry (id) ON DELETE CASCADE
);

INSERT INTO rider_details (id, first_name, last_name, profile_avatar_url, gender, email, date_of_birth) VALUES
(6, 'Okuns', 'Babatunde', 'https://res.cloudinary.com/rider_avatars/image/upload/w_250,h_250,c_fill/headshot_1.jpg', 'male', 'okunsb@gmail.com', '1989-11-20'),
(2, 'Steven', 'Okafor', 'https://res.cloudinary.com/rider_avatars/image/upload/w_250,h_250,c_fill/headshot_2.jpg', 'male', 'steve.okaffor@gmail.com', '1991-08-17'),
(8, 'Chidinma', 'Okondor', 'https://res.cloudinary.com/rider_avatars/image/upload/w_250,h_250,c_fill/headshot_3.jpg', 'female', 'chisweete@yahoo.com', '1991-05-30'),
(5, 'Tania', 'Beyshima', 'https://res.cloudinary.com/rider_avatars/image/upload/w_250,h_250,c_fill/headshot_4.jpg', 'female', 'beyshim.tania2hotmail.com', '1995-03-26');

INSERT INTO driver_details (id, first_name, last_name, insurance_policy_number, insurance_policy_broker, profile_avatar_url, gender, email, date_of_birth, years_of_experience) VALUES
(2, 'Rueben', 'George', '5940044883', 'ARK-AIICO', 'https://res.cloudinary.com/driver_avatars/image/upload/w_250,h_250/sample_1.jpg', 'male', 'reuby445@gmail.com', '1997-11-14', '4'),
(4, 'Adeolu', 'Obalende', '9901237754', 'Applus-Leadway', 'https://res.cloudinary.com/driver_avatars/image/upload/w_200,h_200/sample_2.jpg', 'male', 'adeobale.nde@example.com', '1999-03-22', '3'),
(1, 'Felix', 'Obioha', '6543280117', 'NorthLink-NEM', 'https://res.cloudinary.com/driver_avatars/image/upload/w_250,h_250/sample_3.jpg', 'male', 'felix23@gmail.com', '1990-08-15', '5'),
(5, 'Sandra', 'Arubojah', '9840021365', 'ARK-AIICO', 'https://res.cloudinary.com/driver_avatars/image/upload/w_260,h_260/sample_4.jpg', 'female', 'sandra.arubo@yahoo.com', '1993-12-06', '2');

INSERT INTO user_accounts (id, email, password, full_name, profile_avatar_url, last_login_at, email_verified, account_type, rider_id, driver_id) VALUES
(3, 'felix23@gmail.com', 'hash_passw_z3', 'Felix Obioha', 'https://res.cloudinary.com/driver_avatars/image/upload/w_250,h_250/sample_3.jpg', '2025-09-12 13:47:10', 1, 'driver', NULL, 1),
(6, 'sandra.arubo@yahoo.com', 'hash_pasw_q0', 'Sandra Arubojah', 'https://res.cloudinary.com/driver_avatars/image/upload/w_260,h_260/sample_4.jpg', '2025-11-02 08:12:56', 1, 'driver', NULL, 5),
(7, 'reuby445@gmail.com', 'hash_pass_e8', 'Rueben George', 'https://res.cloudinary.com/driver_avatars/image/upload/w_250,h_250/sample_1.jpg', '2024-12-11 15:40:16', 0, 'driver', NULL, 2),
(11, 'adeobale.nde@example.com', 'hash_pass_t5', 'Adeolu Obalende', 'https://res.cloudinary.com/driver_avatars/image/upload/w_200,h_200/sample_2.jpg', '2025-10-31 10:24:22', 1, 'driver', NULL, 4),
(10, 'chisweete@yahoo.com', 'hash_pass_k9', 'Chidinma Okondor', 'https://res.cloudinary.com/rider_avatars/image/upload/w_250,h_250,c_fill/headshot_3.jpg', '2025-11-02 16:33:11', 1, 'rider', 8, NULL);

INSERT INTO vehicles (id, vehicle_type, owner_id, maker_specs, engine_specs, fueltank_specs, plate_number, color, model_year) VALUES
(2, 'car', 1, 'toyota-corolla', 'v6-single-transmission_4cyl', '100l-guage', 'BEN-315HM', 'red', '2019'),
(3, 'car', 5, 'honda-civic', 'v6-single-transmission_4cyl', '120l-guage', 'KJA-193AA', 'black', '2014'),
(10, 'car', 4, 'toyota-camry', 'v6-single-transmission_4cyl', '100l-guage', 'GAK-874XH', 'cream', '2012');

INSERT INTO rides (id, vehicle_id, status_cancelled, status_completed, status_started, departure_time, arrival_time) VALUES
(3, 3, 0, 1, 1, '2025-11-02 17:26:50', '2025-11-02 18:12:08');

INSERT INTO booking_requests (id, booker_id, status_confirmed, status_cancelled, status_executed, payment_type, booked_on, has_multiple_stops, payment_currency, surge_multiplier, estimated_base_fare) VALUES
(2, 8, 1, 0, 1, 'card', '2025-11-02 17:04:19', 0, 'NGN-kobo', 0.1, 150000);

INSERT INTO ride_bookings (booking_id, ride_id, driver_id, rider_and_payer_id, amount_paid, payment_currency, estimated_payable_fare, status) VALUES
(2, 3, 5, 2, 250000, 'NGN-kobo', JSON_ARRAY(150000, 320000), 'ended');

INSERT INTO ride_routes (id, ride_id, driver_id, rider_id, country, origin_latlng, destination_latlng) VALUES
(1, 3, 5, 8, 'Nigeria', POINT(40.71727401 -74.00898606), POINT(42.71924401 -64.02896677));

-- Fetch all completed rides for the rider (id = 2) showing key summary info for the history screen.
SELECT
    RB.booking_id,
    R.id AS ride_id,
    R.created_at AS booking_date,
    R.arrival_time AS completion_time,
    RB.amount_paid,
    RB.payment_currency,
    CONCAT(DD.first_name, ' ', DD.last_name) AS driver_name,
    RR.rating AS rider_given_rating
FROM ride_bookings RB
INNER JOIN rides R ON RB.ride_id = R.id
INNER JOIN driver_details DD ON RB.driver_id = DD.id
LEFT JOIN ride_reviews RR ON R.id = RR.ride_id AND RR.reviewer_id = 2
WHERE RB.rider_and_payer_id = 2 -- Assumed logged-in Rider ID
AND RB.status = 'ended'
ORDER BY R.created_at DESC;

-- Fetch the average rating and total review count for a drivers' (id = 5) profile card.
-- @NOTE: Calculate average rating by converting the ENUM 'X-star' `rating` column on the `ride_reviews` table to a number
SELECT
    DD.id AS driver_id,
    CONCAT(DD.first_name, ' ', DD.last_name) AS driver_full_name,
    DD.years_of_experience,
    DD.profile_avatar_url,
    AVG(CAST(SUBSTRING_INDEX(RR.rating, '-', 1) AS DECIMAL(2, 1))) AS average_rating,
    COUNT(RR.id) AS total_reviews_count
FROM driver_details DD
INNER JOIN ride_bookings RB ON DD.id = RB.driver_id
INNER JOIN ride_reviews RR ON RB.ride_id = RR.ride_id
WHERE DD.id = 5 -- Assumed logged-in Driver ID
GROUP BY DD.id, DD.first_name, DD.last_name, DD.years_of_experience, DD.profile_avatar_url;


-- Fetch 10 most recent messages for the active conversation thread between a rider an a driver for a given ride (id = 3).
SELECT
    CC.id AS message_id,
    CC.content as message_text,
    UA.full_name AS author_name,
    UA.account_type AS author_type,
    CASE
        WHEN UA.account_type = 'driver' THEN DD.profile_avatar_url
         ELSE RD.profile_avatar_url
    END AS author_avatar_url
FROM cached_ride_booking_convo_thread_messages CC
INNER JOIN user_accounts UA ON CC.author_id = UA.id
LEFT JOIN rider_details RD ON UA.rider_id = RD.id
LEFT JOIN driver_details DD ON UA.driver_id = DD.id
WHERE CC.booking_id = 2 -- Active Booking ID
AND CC.ride_id = 3    -- Active Ride ID
ORDER BY CC.id DESC
LIMIT 10;


-- Fetch the full financial breakdown for a completed ride (id = 4) for a given rider (id = 2).
SELECT
    BR.id AS booking_id,
    BR.estimated_base_fare,
    BR.surge_multiplier,
    RB.amount_paid AS final_amount_paid,
    RB.payment_currency,
    BR.booked_on,
    R.arrival_time AS ride_completion_time,
    CONCAT(DD.first_name, ' ', DD.last_name) AS driver_name
FROM booking_requests BR
INNER JOIN ride_bookings RB ON BR.id = RB.booking_id
LEFT JOIN rides R ON RB.ride_id = R.id
INNER JOIN driver_details DD ON RB.driver_id = DD.id
WHERE BR.booker_id = 2 -- Assumed logged-in Rider ID
AND RB.status = 'ended'
AND BR.status_confirmed = 1
AND BR.status_executed = 1
ORDER BY R.arrival_time DESC
LIMIT 1;


-- Fetch a list of all available drivers nearest to a given pickup point
-- @NOTE: Calculates the distance from a fixed point (Rider's Location)
-- @NOTE: This calculation requires a spatial index and an actual location function (e.g., ST_Distance_Sphere in MySQL 8+)
-- @HINT: `POINT(40.7, -74.0)` is the estimated location of the edge of the geo fence at the time the booking request is made
-- @HINT: `POINT(35.6, -42.1)` is the fixed current location of the rider at the time the booking request is made
SELECT
    CONCAT(V.maker_specs, '; ', V.model_year, '; ', V.engine_specs) AS vehicle_model,
    V.color,
    V.plate_number,
    CONCAT(DD.first_name, ' ', DD.last_name) AS driver_name
FROM driver_details DD
INNER JOIN vehicles V ON DD.id = V.owner_id
WHERE V.id IN (SELECT vehicle_id FROM rides WHERE status_started <> 1 OR status_complete = 1) -- Only vehicles NOT on a started ride or on a completed ride
WHERE DD.id IN (SELECT driver_id FROM ride_bookings WHERE status = 'ended' OR status = 'assigned') -- Only drivers NOT currently executing a booked ride
AND ( ST_Distance_Sphere(POINT(40.7, -74.0), POINT(35.6, -42.1)) * .000621371192 ) <= 1 -- within a 1-mile radius
LIMIT 20;


-- Fetch a list view of all the completed reviews and ratings given for a given rider (id = 3).
SELECT
    RR.rating,
    RR.comment,
    RR.reviewer_id,
    RD.first_name AS reviewer_rider_name,
    DD.first_name AS reviewed_driver_name
FROM ride_reviews RR
INNER JOIN rides R ON RR.ride_id = R.id
INNER JOIN rider_details RD ON RR.reviewer_id = RD.id
INNER JOIN ride_bookings RB ON RR.ride_id = RB.ride_id
INNER JOIN driver_details DD ON RB.driver_id = DD.id
WHERE RR.ride_id = 3;


-- Shows the driver their total earnings and overall rating
SELECT
    CONCAT(DD.first_name, ' ', DD.last_name),
    COUNT(RB.ride_id) AS total_rides_completed,
    SUM(RB.amount_paid) AS total_earnings,
    RB.payment_currency AS currency,
    (SELECT AVG(CAST(SUBSTRING_INDEX(rating, '-', 1) AS DECIMAL(2, 1)))
        FROM ride_reviews RR
        INNER JOIN ride_bookings RB2 ON RR.ride_id = RB2.ride_id
        WHERE RB2.driver_id = DD.id
    ) AS average_rating
FROM driver_details DD
LEFT JOIN ride_bookings RB ON DD.id = RB.driver_id
WHERE DD.id = 5
AND RB.payment_currency = 'NGN-kobo'
GROUP BY DD.id, DD.first_name
ORDER BY RB.created_at
LIMIT 20;