So, here is a situation I am mulling over involving sequential entities. I can think of one or two solutions for it, but I suspect it is a fairly standard design pattern and I don't want to re-invent the wheel!:
For an airline ticketing company, I am modeling these entities:
Trips
Routings
Cities
A "Trip" is defined primarily by a departure city and a destination city.
A "Routing" is defined by the sequence of cities passed-through en route to the final destination.
Any given trip can have multiple possible routings; eg Ottawa-Baghdad might have the routings Ottawa-Toronto-Amsterdam-Paris-Baghdad; or Ottawa-NewYork-Frankfurt-Baghdad, etc. The number of possible routings for a given trip is unlimited, and the number of possible pass-thru cities for a given routing is unlimited.
It is easy enough to see that any given "Routing" will be associated with many cities. The trickier thing is that these cities are connected in a particular order for each routing.
In a way, a routing is really just a chain of self-referential "Legs", where a leg is just a jaunt between two cities. Thus a "Routing" has many "Legs"; a "Leg" always has a parent and a child leg and would be modeled as a standard self-referential entity, like "Employee" and "Manager".
While I think this would work, it seems pretty unwieldy, given that a common activity is snapping up all routings for a given trip, and this would involve unfurling this unintuitive "Leg" table.
Is there a standard approach for modeling such a situation?
Any other ideas?
Thanks!
For an airline ticketing company, I am modeling these entities:
Trips
Routings
Cities
A "Trip" is defined primarily by a departure city and a destination city.
A "Routing" is defined by the sequence of cities passed-through en route to the final destination.
Any given trip can have multiple possible routings; eg Ottawa-Baghdad might have the routings Ottawa-Toronto-Amsterdam-Paris-Baghdad; or Ottawa-NewYork-Frankfurt-Baghdad, etc. The number of possible routings for a given trip is unlimited, and the number of possible pass-thru cities for a given routing is unlimited.
It is easy enough to see that any given "Routing" will be associated with many cities. The trickier thing is that these cities are connected in a particular order for each routing.
In a way, a routing is really just a chain of self-referential "Legs", where a leg is just a jaunt between two cities. Thus a "Routing" has many "Legs"; a "Leg" always has a parent and a child leg and would be modeled as a standard self-referential entity, like "Employee" and "Manager".
While I think this would work, it seems pretty unwieldy, given that a common activity is snapping up all routings for a given trip, and this would involve unfurling this unintuitive "Leg" table.
Is there a standard approach for modeling such a situation?
Any other ideas?
Thanks!
