learning-it by example

#273 Data modelling 6.7 min

Data Modelling Interview: Design a Ride Hailing Schema

On one made-up day, a wide table I wrote to fail could not find the busiest hour, overstated one driver's earnings by 54.18, and gave a surge of 1.0 where the schema said 1.8.

1. In the ride hailing schema, how are changes such as a trip moving through statuses, a driver moving or prices changing stored?
2. Why does the schema record money as payments of kind charge or refund in whole pence, rather than one edited total?
3. On a made-up day of 18 ride hailing trips in SQLite, what did the good schema report as the busiest hour, and what did the one wide table report?
4. In a made-up SQLite ride hailing day, why was driver Zoe's earnings figure in the one wide table 54.18 pounds too high?
5. What does the surge history table let you answer that a single current surge value cannot?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Compare the two designs

    Copy the code folder first. Run python run.py good, then python run.py poor. Write down the four questions the program asks, and for each one say which design answers it correctly. Everything is made up and runs in memory.

    A hint

    The poor design is one wide table written to fail in these ways.

  2. Follow a cancelled trip

    In run.py, change TRIP = 2 to TRIP = 5 and run python run.py good. Predict how the history differs from trip 2. When I ran it, trip 5 showed requested at 08:40, accepted at 08:42 and cancelled at 08:44, with no completed line, and a surge of 1.8 when requested. Change TRIP back to 2 afterwards, because the next task refers to trip 2.

    A hint

    A cancelled trip has a different list of statuses.

  3. Change the morning surge

    In data.py, change the 1.8 in the SURGE row (1, "08:00", "08:45", 1.8) to 2.5. Predict what the surge line for trip 2 prints, then run python run.py good. When I ran it, the last line read 2.5. Some earnings changed too, because trips requested in that window were quoted at the new surge. Run python run.py poor and see whether its surge line changes.

    A hint

    The wide table keeps only the current value of each zone.

  4. Add a busy evening hour

    In data.py, change the start of HOURS from [7, 8, to [18, 18, 8, so that two more requests fall at 18 and one at 7 is dropped. Predict the busiest hour, then run python run.py good. When I ran it, the busiest hour was 18 with 6 trips. The earnings change too, because the random trips are made again.

    A hint

    The busiest hour query groups requests by the hour of the request.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

One made-up day of 18 trips; the wide table was written to show these failures; the interview questions are my own choice; no platform commission in earnings; no time zones; ratings are stored but not queried; not any real company's design.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos