🐛📄 – Resolving Conflict between Multiple Operators + Primary Key in trips_performed

[chrisyamas]

The Problem: Conflict between TCRP recommendation and TIDES spec

There is a contradiction between the TCRP paper guidance and the TIDES specification regarding how to handle multiple operators for a single trip:

1. TCRP Paper (Section 6.8.1) states that if there are multiple operators for a given trip, "the single trip should be split into two records in the Trips_performed table, one representing each leg of the trip operated by a single operator."

2. TIDES Specification defines the primary key for the trips_performed table as the combination of trip_id_performed and service_date, which would be violated if a single trip is split into multiple records as suggested by the TCRP paper.

This contradiction creates implementation challenges for agencies that need to track operator changes during trips.

Current Specification

The current TIDES specification for the trips_performed table:

• Defines the primary key as (trip_id_performed, service_date)
• Does not provide a mechanism for representing multiple operators for a single trip
• Does not address the guidance provided in the TCRP paper

WMATA's Implementation Experience

In WMATA's operations:

• Operator changes mid-trip do occur, though it's not clear if these are scheduled or only happen in unexpected circumstances (e.g., illness)
• The current implementation approach for handling these cases is not well-documented
• There may be duplicate records in the bus state data, but it's unclear if these are related to operator changes

Proposed Solutions

We propose several potential solutions to resolve this contradiction:

Option 1: Add Operator Sequence to Primary Key

Modify the primary key to include an operator sequence number:

Primary Key: (trip_id_performed, service_date, operator_sequence)

Where operator_sequence would be a zero-based or one-based index indicating the order of operators for the trip (0, 1, 2, etc. or 1, 2, 3, etc.).

Option 2: Create New Trip ID Performed

When an operator changes mid-trip, create a new trip_id_performed value while maintaining the same trip_id_scheduled:

Trip 123, Operator A → trip_id_performed = "123A"
Trip 123, Operator B → trip_id_performed = "123B"

This approach preserves the primary key constraint but may create challenges for tracking the complete trip.

Option 3: Add Trip Version Field

Add a trip_version or similar field to track changes to a trip, including operator changes:

Primary Key: (trip_id_performed, service_date, trip_version)

This approach is more general and could accommodate other changes beyond operator changes.

Impact

Resolving this contradiction would:

1. Provide clear guidance for implementing agencies
2. Ensure data integrity in the trips_performed table
3. Enable accurate tracking of operator changes
4. Align the TIDES specification with the TCRP guidance

Questions for Consideration

1. Are there other fields besides operator_id that might change mid-trip and require similar handling?
2. How common are mid-trip operator changes across transit agencies?
3. What is the analytical importance of tracking these changes?
4. Would any of the proposed solutions create challenges for existing implementations?

Additional Context

This issue highlights a broader question about how to handle changes to trip attributes mid-trip while maintaining data integrity. The solution adopted should balance the need for accurate representation of operations with the practical constraints of database design and implementation.

[botanize]

I think we can resolve this if we combine my suggestion in #220 for tracking operator (and other assignments) with defining trip_id_performed as a vehicle-trip, not vehicle-operator.

[jaygordon]

I aggree with @botanize that this can be handled with #220, and that trips_performed shouldn't be split just because operators change.