Re: Layout of "Itinerary" Record - A Database Design Question

Hmmm.

Option 1 requires calculations and the possibility of a link to another destination. (same day, closest arrival time)

Option 2 requires 2 indexes and the possibility of a link to another destination. (To_Location_ID becomes From_Location_ID but can link to other locations as well); indezes then should contain at least the 2 Location IDs.

Location A points to Location B and from Location B you can go to Location C, D, E and A.

Option 2 is easier to implement.

Does it always have to be visa versa?

Just my qick thoughts.


Regards,

Carel Teijgeler



Op 7-6-2016 om 23:23 schreef Nathan Andelin:

We've come up with 2 alternatives for trip itinerary records and would like
to settle on one or the other:

Option 1: delineate one row for each "stop" in the route:

Surrogate_Key.
Location_ID (refers to a row in "location file").
Arrival_Date.
Arrival_Time.
Number_Of_Minutes_At_This_Stop.

Three (3) rows would be required to delineate the "steps" to a single
destination and back. Some users prefer this type of listing on screens.

Option 2: delineate one row for each "from" and "to" location in the route:

Surrogate_Key_Value.
From_Location_ID (refers to a row in "location file").
From_Location_Departure_Date.
From_Location_Departure_Time.
To_Location_ID (refers to a row in "location file").
To_Location_Arrival_Date.
To_Location_Arrival_Time.

Two (2) rows would be required to delineate the "steps" to a single
destination and back. Some users prefer this type of listing on screens.

How should we settle this debate?