The Other Seat

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
senior
Asked in
Lyft

Problem

We run a ride-hailing marketplace where the same person can sign up to drive and to ride, so the model has to capture both roles without splitting one human into two records. Design the entities for drivers, riders, vehicles, and the trips that connect them, knowing that every completed trip records the fare charged, the surge multiplier that was in effect, the specific vehicle used, and a separate rating in each direction. A driver may switch vehicles between trips, and each person's displayed rating is derived from the individual ratings captured on their trips, so the per-trip ratings stay the source of truth even if a current average is also cached on the profile for fast reads.

Worked solution and explanation

One human, two seats

This is an identity-unification problem dressed up as 'model drivers and riders'. Most candidates draw a drivers table and a riders table as two separate islands, and that fails in two places. First, the person who drives on Saturday and rides on Friday becomes two unrelated rows. Second, the trip that connects them needs a foreign key into a different table for each side. The real skill is seeing one `dim_users` with role subtypes, and a trip fact that points at that single identity from both sides. If you get it wrong, 'total spend and earnings per person' turns into a fuzzy-matching project on phone numbers.

Four decisions, in order

Step 1: Unify identity in dim_users

Put one row per human in dim_users, holding name, phone and signup. Driver-only fields (license, status, current vehicle) go in dim_driver_profiles, and rider-only fields go in dim_rider_profiles. Both subtypes use user_id as their PK. A dual-role person has one identity row and two profile rows.

Step 2: Declare fact_trips at one row per completed trip

The trip is the bridge. driver_user_id and rider_user_id are two distinct FKs, and each one says which seat that person occupied. That captures the many-to-many between drivers and riders with no separate bridge table.

Step 3: Snapshot what drifts onto the trip

fare_amount, surge_multiplier and vehicle_id are all written at trip time. current_vehicle_id on the driver profile answers a different question: what the driver drives today. A trip from last spring has to keep the car that was actually used.

Step 4: Capture both ratings at trip grain

Store rating_of_driver and rating_of_rider on fact_trips. The displayed rating is an aggregate over these values. You can cache an average on the profile for fast reads, as long as the per-trip values remain the source it is rebuilt from.

`dim_users` is the only identity. Both profiles are subtypes keyed by `user_id`, and `fact_trips` references that identity twice and snapshots `vehicle_id`, fare, surge and both ratings.
  • dim_users: user_id BIGINT PK, full_name TEXT, phone TEXT, home_city TEXT, signup_at TIMESTAMP
  • dim_driver_profiles: user_id BIGINT PK, license_number TEXT, current_vehicle_id BIGINT FK, status TEXT, driver_since DATE
  • dim_rider_profiles: user_id BIGINT PK, default_payment_id BIGINT, rider_since DATE
  • dim_vehicles: vehicle_id BIGINT PK, owner_user_id BIGINT FK, make TEXT, model TEXT, plate TEXT, seat_capacity INT
  • fact_trips: trip_id BIGINT PK, driver_user_id BIGINT FK, rider_user_id BIGINT FK, vehicle_id BIGINT FK, requested_at TIMESTAMP, completed_at TIMESTAMP, fare_amount DECIMAL, surge_multiplier DECIMAL, rating_of_driver INT, rating_of_rider INT
  • dim_driver_profiles.user_id references dim_users.user_id
  • dim_rider_profiles.user_id references dim_users.user_id
  • dim_vehicles.owner_user_id references dim_users.user_id
  • dim_driver_profiles.current_vehicle_id references dim_vehicles.vehicle_id
  • fact_trips.driver_user_id references dim_users.user_id
  • fact_trips.rider_user_id references dim_users.user_id
  • fact_trips.vehicle_id references dim_vehicles.vehicle_id
Reference schema DDL
CREATE TABLE dim_users (
    user_id    BIGINT PRIMARY KEY,
    full_name  TEXT,
    phone      TEXT,
    home_city  TEXT,
    signup_at  TIMESTAMPTZ
);

CREATE TABLE dim_vehicles (
    vehicle_id     BIGINT PRIMARY KEY,
    owner_user_id  BIGINT REFERENCES dim_users(user_id),
    make           TEXT,
    model          TEXT,
    plate          TEXT,
    seat_capacity  INT
);

CREATE TABLE dim_driver_profiles (
    user_id            BIGINT PRIMARY KEY REFERENCES dim_users(user_id),
    license_number     TEXT,
    current_vehicle_id BIGINT REFERENCES dim_vehicles(vehicle_id),
    status             TEXT,
    driver_since       DATE
);

CREATE TABLE dim_rider_profiles (
    user_id            BIGINT PRIMARY KEY REFERENCES dim_users(user_id),
    default_payment_id BIGINT,
    rider_since        DATE
);

CREATE TABLE fact_trips (
    trip_id          BIGINT PRIMARY KEY,
    driver_user_id   BIGINT REFERENCES dim_users(user_id),
    rider_user_id    BIGINT REFERENCES dim_users(user_id),
    vehicle_id       BIGINT REFERENCES dim_vehicles(vehicle_id),
    requested_at     TIMESTAMPTZ,
    completed_at     TIMESTAMPTZ,
    fare_amount      NUMERIC,
    surge_multiplier NUMERIC,
    rating_of_driver SMALLINT,
    rating_of_rider  SMALLINT
);

fact_trips is one row per completed trip, with two FKs into dim_users.

Unified users with role subtypes
One dim_users row per person. driver_user_id and rider_user_id both resolve to it, so dual-role activity rolls up under one key. The cost is one extra join to reach role-only fields.
Separate drivers and riders tables
dim_drivers and dim_riders each have their own key. That is quicker to sketch, but a dual-role person becomes two rows that never reconcile, and the trip's FKs point at different tables for each side.
Displayed driver rating, derived from trips
SELECT
    t.driver_user_id,
    ROUND(AVG(t.rating_of_driver), 2) AS driver_rating,
    COUNT(t.rating_of_driver) AS rated_trips
FROM fact_trips t
GROUP BY t.driver_user_id

Change rating_of_driver to rating_of_rider and join on rider_user_id to get the rider-side rating.

Common follow-up questions