Design this schema: a ride-hailing app

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

A rider taps "Request". For a few seconds the ride has no driver, then it has one, then maybe it is cancelled. Design the tables so every one of those moments is a valid row, and the app can still answer "how much did each driver earn this week?" in one query.

You'll learn to

• Model a ride as a row that moves through states, with NULLs that mean "not yet"
• Use a weak entity (ratings) and a partial index where they fit
• Keep high-speed data (live locations) out of the core tables, and say why
• Write the key ride-hailing queries against your own schema

Learn

Step 1: the requirements

Agree these with the interviewer first:

• Riders and drivers sign up; a driver works in one city and owns one or more vehicles.
• A rider requests a ride. A driver accepts it, then completes it, or someone cancels.
• A completed ride has a distance and a fare.
• After a ride, the rider rates the driver and the driver rates the rider, once each.
• Hot queries: a rider's trips, a driver's earnings this week, open requests in a city, cancellation rate per city, average rating per driver.

Ask about scale: live driver locations arrive every few seconds per driver. That changes the design.

Step 2: the ride-hailing ER diagram

An ER diagram of a ride-hailing app: a rider requests many rides, a driver drives many rides, a vehicle is used for many rides, a driver owns many vehicles, a city has many drivers and many rides, and each ride has ratings as a weak entity identified by the ride and who rated.

drives and used_for are not total on the ride side: a ride that is only requested has no driver yet, so rides.driver_id is nullable. A rating is a weak entity: "the rider's rating" only makes sense for one ride, so its key is (ride_id, rated_by).

Step 3: the tables

CREATE TABLE cities (
  city_id   INT PRIMARY KEY,
  name      VARCHAR(40) NOT NULL UNIQUE,
  currency  CHAR(3) NOT NULL
);
CREATE TABLE riders (
  rider_id   INT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  phone      VARCHAR(20) NOT NULL UNIQUE,
  joined_at  TIMESTAMP NOT NULL
);
CREATE TABLE drivers (
  driver_id  INT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  city_id    INT NOT NULL REFERENCES cities(city_id),
  joined_at  TIMESTAMP NOT NULL
);
CREATE TABLE vehicles (
  vehicle_id  INT PRIMARY KEY,
  driver_id   INT NOT NULL REFERENCES drivers(driver_id),
  plate       VARCHAR(12) NOT NULL UNIQUE,
  kind        VARCHAR(10) NOT NULL CHECK (kind IN ('economy', 'xl', 'premium'))
);
CREATE TABLE rides (
  ride_id       INT PRIMARY KEY,
  rider_id      INT NOT NULL REFERENCES riders(rider_id),
  driver_id     INT REFERENCES drivers(driver_id),
  vehicle_id    INT REFERENCES vehicles(vehicle_id),
  city_id       INT NOT NULL REFERENCES cities(city_id),
  status        VARCHAR(10) NOT NULL CHECK (status IN ('requested', 'accepted', 'completed', 'cancelled')),
  requested_at  TIMESTAMP NOT NULL,
  accepted_at   TIMESTAMP,
  completed_at  TIMESTAMP,
  distance_km   NUMERIC(6,2),
  fare          NUMERIC(8,2),
  CHECK (accepted_at >= requested_at),
  CHECK (status <> 'completed' OR (driver_id IS NOT NULL AND fare IS NOT NULL))
);
CREATE TABLE ratings (
  ride_id   INT REFERENCES rides(ride_id) ON DELETE CASCADE,
  rated_by  VARCHAR(6) CHECK (rated_by IN ('rider', 'driver')),
  stars     INT NOT NULL CHECK (stars BETWEEN 1 AND 5),
  PRIMARY KEY (ride_id, rated_by)
);

SELECT table_name, COUNT(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;

Two CHECKs on rides encode the business rules: you cannot accept before the request, and a completed ride must have a driver and a fare. The database rejects a broken row even if the app has a bug.

A ride moves through states

One row per ride, updated as it moves. The timestamps keep the timeline, so "minutes to accept" is accepted_at - requested_at. If the business needs every change (re-assigned drivers, price updates), add a ride_events table with one row per change instead of overwriting.

Step 4: normalise, and what stays out

• City sits on drivers and on rides. They are different facts: where the driver is based, and where this trip happened. A London driver can take a trip at the airport outside the city.
• Fare is stored, not computed, because pricing rules and surge change. Like the price on an order line, it is the amount agreed for this trip.
• Ratings get their own table, not rider_stars and driver_stars columns on rides, so each rating is one row with a clean key.
• Live locations (every few seconds per driver) do not go in these tables. Millions of tiny updates would bloat them; they belong in a fast in-memory or time-series store.

Real ride-hailing apps keep live driver positions in an in-memory geo index that answers "drivers within 2 km" in milliseconds. Only the trip itself, with its start, end, fare and rating, is written to the relational database, where it must be correct and kept for years.

Step 5: indexes for the hot queries

-- a rider's trips, newest first
CREATE INDEX idx_rides_rider ON rides (rider_id, requested_at DESC);
-- a driver's trips and weekly earnings
CREATE INDEX idx_rides_driver ON rides (driver_id, completed_at);
-- open requests in a city: a partial index, only the few waiting rows
CREATE INDEX idx_open_requests ON rides (city_id, requested_at)
  WHERE status = 'requested';

SELECT indexname
FROM pg_indexes
WHERE tablename = 'rides'
ORDER BY indexname;

The partial index holds only rides that are still waiting, so it stays tiny even with millions of finished trips. A query must repeat WHERE status = 'requested' for the planner to use it.

Step 6: prove it with a key query

-- how long riders waited for a driver
SELECT r.ride_id, c.name AS city,
       EXTRACT(EPOCH FROM r.accepted_at - r.requested_at) / 60 AS minutes_to_accept
FROM rides AS r
JOIN cities AS c ON c.city_id = r.city_id
WHERE r.accepted_at IS NOT NULL
ORDER BY r.ride_id;

Subtracting two timestamps gives an interval; EXTRACT(EPOCH FROM …) turns it into seconds, and / 60 into minutes. Rides that were never accepted have a NULL accepted_at and are filtered out.

Strong answers here say what NULL means in each column ("no driver yet"), put rules in CHECKs, keep fares as stored facts, and move live locations out of the relational tables. Mention a partial index for open requests and you sound like someone who has run this in production.

In one line

Ride-hailing: one rides row per trip with status and timestamps, NULL driver until accepted, CHECKs for the rules, ratings as a weak entity keyed (ride_id, rated_by), live locations outside the core tables.

Interview corner

How would you model the life of a ride?

One rides row with a status (requested, accepted, completed, cancelled) and a timestamp per step. driver_id and fare are NULL until they are known, and CHECK constraints stop invalid states, such as a completed ride with no driver. If every change must be kept, add a ride_events table.

Where would you store live driver locations?

Not in the relational trip tables. They change every few seconds and are only useful for a short time, so they go to an in-memory or time-series store with a geo index. The trip table stores the start and end points and the result.

Which indexes would you add for this app?

rides(rider_id, requested_at) for trip history, rides(driver_id, completed_at) for earnings, and a partial index on rides(city_id, requested_at) WHERE status = 'requested' for matching open requests. Each one is tied to a query the app runs constantly.