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

Interesting question. It would help to know some more about the usage case.

The use case is to schedule itineraries for bus trips, to assign drivers
and buses to trips, to calculate or derive miles and minutes between legs,
to calculate the cost of trips, and such.

Option 1 would need some kind of sequence to indicate what order the stops

are visited in if there is more than one stop on the journey/route, though
conceivably you could use the arrival time for the same purpose.

We were thinking of using the surrogate key to sequence stops under option
#1.

I favour option 2 in that is seems to me it would better support a journey

other than a single return journey. Calculating durations would also be
simpler than messing with arrival times in separate records.

Thanks for your point of view.