A database schema for a ride hailing app that specializes in linking riders to drivers on frequently-travelled transport routes based on Google Maps info using either MySQL, SQLite or PostgreSQL as primary database

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(100) NOT NULL,
    last_name varchar(100) NOT NULL,
    profile_avatar_url text,
    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(100) NOT NULL,
    last_name varchar(100) NOT NULL,
    insurance_policy_number varchar(18) NOT NULL,
    insurance_policy_broker varchar(40) NOT NULL,
    profile_avatar_url text,
    gender enum('male', 'female') NOT NULL,
    years_of_experience char(2) NOT NULL,
    CONSTRAINT driverunq UNIQUE (id, first_name, last_name),
    PRIMARY KEY (id)
);

CREATE TABLE user_accounts (
    id bigint NOT NULL,
    email varchar(50) NOT NULL,
    full_name varchar(150) NOT NULL,
    last_login_at timestamp,
    profile_avatar_url text,
    email_verified tinyint(1) NOT NULL DEFAULT 0,
    account_type enum('driver', 'rider') NOT NULL,
    rider_id bigint,
    driver_id bigint,
    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
);

CREATE TABLE vehicles (
    id bigint NOT NULL,
    vehicle_type enum('car', 'truck', 'jeep', 'motor-cycle') NOT NULL,
    owner_id bigint NOT NULL,
    maker_specs enum('toyota-camry', 'toyota-corolla' 'lexus', 'nissan', 'peugeot-406', 'peugeot-302',  'audi', 'benz-C-class', 'benz-S-class', 'bmw') NOT NULL,
    engine_specs enum('v6-single-transmission', 'v8-double-transmission') 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, -- ('{ "driver_license": true, "vio-inspection": 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,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    departure_time DATETIME,
    arrival_time DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(vehicle_id) REFERENCES rider_vehicles(id) ON DELETE CASCADE
);

CREATE TABLE bookings (
    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 decimal NOT NULL DEFAULT 0.0,
    surge_multiplier decimal NOT NULL DEFAULT 0.0,
    route_info json NOT NULL, -- ('{ "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
);

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 NOT NULL,
    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,
    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_details(id) ON DELETE CASCADE,
    FOREIGN KEY(rider_and_payer_id) REFERENCES rider_details(booker_id) ON DELETE CASCADE
);

CREATE TABLE 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 mediumtext,
    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,
    city varchar(20) 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 ();

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), 'assigned');

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