Production database schema for an Uber-like ride-sharing platform featuring driver and vehicle management, real-time ride lifecycle tracking, GPS route logging, dynamic surge pricing, fare estimation, payment processing, mutual rating system, driver earnings, promotions, and support ticket management.
One account can be a rider and a driver, so users holds both and driver_profiles holds only what is true while driving — licence, status, rating, and the current position. That last pair of columns is the unusual one: current_latitude and current_longitude are overwritten constantly, which is why they live on the driver rather than in the tracking table.
A ride is a row that fills in over time. requested_at, accepted_at, started_at, completed_at and cancelled_at are separate timestamps rather than a status history, so the lifecycle is readable from one row and 'how long did the driver take to accept' is a subtraction.
Money is recorded three times and they are three different numbers. rides holds the fare quoted and charged, payments holds what the passenger actually paid, and driver_earnings holds gross, commission and net for the driver. Collapsing them would make it impossible to answer where the difference went.
Dispatch needs the latest position of every available driver, and it needs it now. A row appended to ride_tracking every few seconds would mean finding the newest per driver on every dispatch query. Two columns overwritten in place make that a plain index scan. The history is not lost — ride_tracking keeps it, but only during a ride, which is the only time anyone needs the path.
status says where the ride is now; the five timestamps say how it got there. A status history table would answer the same questions with a join and an ordering, and every dashboard — acceptance time, trip duration, cancellation rate — becomes a subtraction between two columns instead. The limitation is that a state cannot be entered twice, which for a ride is a feature.
base_fare, total_fare and surge_multiplier are copied onto the ride rather than recomputed from ride_types. Rates change and surge is momentary, so a ride that cannot reproduce its own price is a ride nobody can explain to a customer. ride_types remains the current rate card, not a historical one.
promotions.uses_count is the cheap check against max_uses, but promo_usages records who redeemed what on which ride. Without it, 'one use per customer' is unenforceable and a disputed discount cannot be traced. The counter is the fast path; the rows are the truth.