Ride-Hailing App ER diagram and database schema

What this schema models

A complete ride-hailing app database design, modeled on how real ride-hailing platforms dispatch and price rides: 27 tables, 21 enums and 58 relationships covering markets, zones and product tiers, versioned rate cards and surge, riders and payment methods, ride requests, fare estimates and dispatch offers, trips, cancellations and two-way ratings, the itemized receipt, the driver earnings ledger and payouts, driver documents and vehicles, and online sessions with live location. THE FOUR DESIGN RULES 1. A REQUEST is not a TRIP. A ride request is the rider asking; a trip exists only once a driver accepts. Requests that find no driver, or are cancelled while searching, stay as requests, and counted per zone they are exactly the demand signal surge pricing needs. 2. Every dispatch offer is a row. Each driver offered the job gets a row that ends accepted, declined, expired or withdrawn, so acceptance rate is a query, and a partial unique index allows only one accepted offer per request: two drivers can never both win the same ride. 3. Prices are locked, then itemized. The fare estimate points at the exact rate card version and surge window it used and copies the multiplier; the trip keeps that multiplier, and the final fare is itemized lines (base, distance, time, surge, fees, tolls, promo, tip, tax). What the rider paid and what the driver earned are separate ledgers, so the platform take is the difference, never a stored column. 4. Latest position and location history are different tables. driver_locations holds one row per online driver, overwritten every few seconds and indexed with GiST for nearest-driver search; driver_location_pings is the append-only GPS stream, partitioned by day and dropped a partition at a time. Dispatch never reads the stream. DETAILS WORTH STEALING - Trips are a status machine with a timestamp per state (accepted, arrived, started, completed, cancelled), so pickup wait and ride time are plain subtractions. - Surge is stored per zone, tier and time window together with the open requests and available drivers that produced it, so any surged price can be explained in a dispute. - Zones are hexagonal grid cells, a fixed grid that keeps surge history comparable over time, and airport zones are flagged for queue-based dispatch. - Cancellations record who cancelled, why, how many seconds after acceptance (checked against the free cancellation window) and the share of the fee that compensates the driver. - Driver documents are reviewed one at a time with expiry dates, and a renewal is a new row, so the verification audit trail survives. - Payments carry an idempotency key, so a retry after a timeout can never charge a rider twice. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: riders, markets and pricing, dispatch, trips, money, drivers and vehicles, and driver supply and location. Every table carries a note. Postgres-flavoured types, with PostGIS geography columns for locations; the rest ports cleanly to MySQL or SQL Server. Useful for developers building a ride-hailing, taxi or on-demand mobility app, and for students who need a realistic ride-hailing app ER diagram for a DBMS project. This is a schema design reference; fare regulation, driver classification and tax rules vary by market and belong in your own business logic.

Tables (27)

Relationships (58)

• dispatch_offers.driver_id references drivers.id many to one
• dispatch_offers.id references trips.dispatch_offer_id one to one
• dispatch_offers.ride_request_id references ride_requests.id many to one
• driver_documents.driver_id references drivers.id many to one
• driver_documents.vehicle_id references vehicles.id many to one
• driver_earnings.driver_id references drivers.id many to one
• driver_earnings.driver_payout_id references driver_payouts.id many to one
• driver_earnings.trip_cancellation_id references trip_cancellations.id many to one
• driver_earnings.trip_id references trips.id many to one
• driver_location_pings.driver_id references drivers.id many to one
• driver_location_pings.trip_id references trips.id many to one
• driver_locations.driver_session_id references driver_sessions.id many to one
• driver_locations.zone_id references zones.id many to one
• driver_payouts.driver_id references drivers.id many to one
• driver_sessions.driver_id references drivers.id many to one
• driver_sessions.service_area_id references service_areas.id many to one
• driver_sessions.vehicle_id references vehicles.id many to one
• drivers.id references driver_locations.driver_id one to one
• drivers.service_area_id references service_areas.id many to one
• fare_estimates.fare_rule_id references fare_rules.id many to one
• fare_estimates.id references ride_requests.fare_estimate_id one to one
• fare_estimates.product_tier_id references product_tiers.id many to one
• fare_estimates.rider_id references riders.id many to one
• fare_estimates.surge_multiplier_id references surge_multipliers.id many to one
• fare_line_items.trip_id references trips.id many to one
• fare_rules.product_tier_id references product_tiers.id many to one
• fare_rules.service_area_id references service_areas.id many to one
• payment_methods.rider_id references riders.id many to one
• payments.parent_payment_id references payments.id many to one
• payments.payment_method_id references payment_methods.id many to one
• payments.ride_request_id references ride_requests.id many to one
• payments.trip_id references trips.id many to one
• promo_codes.service_area_id references service_areas.id many to one
• promo_redemptions.promo_code_id references promo_codes.id many to one
• promo_redemptions.rider_id references riders.id many to one
• promo_redemptions.trip_id references trips.id many to one
• ratings.trip_id references trips.id many to one
• ride_requests.id references trips.ride_request_id one to one
• ride_requests.id references trip_cancellations.ride_request_id one to one
• ride_requests.payment_method_id references payment_methods.id many to one
• ride_requests.pickup_zone_id references zones.id many to one
• ride_requests.product_tier_id references product_tiers.id many to one
• ride_requests.promo_redemption_id references promo_redemptions.id many to one
• ride_requests.rider_id references riders.id many to one
• riders.home_service_area_id references service_areas.id many to one
• saved_places.rider_id references riders.id many to one
• surge_multipliers.product_tier_id references product_tiers.id many to one
• surge_multipliers.zone_id references zones.id many to one
• trips.driver_id references drivers.id many to one
• trips.fare_rule_id references fare_rules.id many to one
• trips.id references trip_cancellations.trip_id one to one
• trips.product_tier_id references product_tiers.id many to one
• trips.rider_id references riders.id many to one
• trips.vehicle_id references vehicles.id many to one
• vehicle_product_tiers.product_tier_id references product_tiers.id many to one
• vehicle_product_tiers.vehicle_id references vehicles.id many to one
• vehicles.driver_id references drivers.id many to one
• zones.service_area_id references service_areas.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Ride-Hailing App database schema?

27 tables and 58 relationships, across 260 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.