Table Schema & Relationships

Data Service
1

Hi, this is a very learning-oriented question but I’m hoping to solidify my understanding of relationships between tables as I build my app. Consider the following two hypothetical tables, “Ride” and “Person”:

image
image913×170 4.66 KB

The business here is one that provides rides to medical patients. For each ride, there is a Driver, who actually drives the car the patient is being transported in, and a Dispatcher, who receives the call requesting a ride and coordinates the operation remotely. In some cases, due to operational logistics, it is possible to have a second driver or dispatcher (e.g. the patient needs to be handed off mid-ride).

The schema is such that there is a table tracking all of the rides and another table that has all of the people, who in this case are all employees and are either Drivers or Dispatchers.

When I think about this from a layperson’s perspective, and disregarding the possibility of the second driver/dispatcher, I think of these as 1:1 relationships. A ride has a single main Driver, which is relatable to the Person table. It also has a single main Dispatcher, which is relatable to the Person table. When I add the possibility of a second driver/dispatcher these are still just 1:1 relations in my head, because they’re all distinct people in distinct roles.

I suspect that in database-world these are many:many relationships though. Is the optimal way to set this up to follow the procedure reviewed here?

If so, how do I track the person type as part of the relationship? In the example of students and courses, all students and all courses are essentially fungible. In my case though the people being assigned to the ride are fundamentally different. One person is the primary Driver, another person is the primary Dispatcher. In some cases there is also a secondary driver and secondary dispatcher. This is not just a collection of fungible items in an order or students in a class.

Can you let me know the best way to structure the schema in this type of situation and how to establish the relations in a way that preserves this distiction between Person “type” and whether they’re the main or backup entity?

2

Hi James,

Let me ask you a few clarifying questions. These will help us making a suggestion for a better design:

  1. Can the same person be a driver for one ride and a dispatcher for another?
  2. Can a ride have more than 2 drivers?
  3. Can a ride have more than 2 dispatchers?
  4. If a ride has 2 dispatchers, do you know the second dispatcher in advance or they are assigned mid-ride?
  5. The same question as above, but it is about drivers.
  6. If a ride has two drivers, what does it mean in the real-world? There are two driver people in the car and they can swap each other according to some policy?

Regards,
Mark

3

Thanks for the reply! I realized today that I may just be able to use a series of 1:1 relationships after all. At least in testing so far it seems to treat each relation separately. That said, it might not be optimal.

To your questions:

  1. No - Drivers and Dispatchers are two different Employee types and wouldn’t change without some type of formal role change. They would only have one type at any given time.
  2. It is not technically impossible but hasn’t happened yet in about 40k total rides. I am willing to disregard the possibility and simply miss the data if that occurs.
  3. Same answer as #2.
  4. We do not know it in advance. It usually happens at a shift change.
  5. For the drivers in the vast majority of cases the second “driver” is actually a passenger doing a ride-along for training purposes. It is theoretically possible that a driver might need to be changed out mid-ride unexpectedly due to some catastrophic issue but that has not occurred yet.
  6. See above, but you’re correct. Driver #2 is actually just a passenger. All individuals in the car need to be recorded however for legal reasons.

Thanks so much!
James

4

Hi James,

Thank you for the answers. I believe the best option is to have 1:1 relationships. The column names would establish the semantics of the relations (as they should): driver1, driver2, dispatcher1, dispatcher2, eetc.

To differentiate drivers from dispatchers, you could introduce a column called “type” or create two separate tables : Drivers and Dispatchers. I recommend the latter approach.

Hope this helps.

Regards,
Mark

5

Great, thank you!