transit fare rules table structure

Transit fares can be associated to routes, in cases where routes have flat fares regardless of boarding and alighting stops. The other possible association is through the existing of fare zones, where trips are charged based on the combination of the transit zones for boarding and alighting.

This table includes both associations, thus records would have either the route_id or origin/destination fields as null.

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
fare_id	INTEGER	NO		Transit_Fare_Attributes(fare_id)	ID of the trip in the format AA000000000000 (Agency)
route_id	INTEGER	YES		Transit_Routes(route_id)	ID of the route to which this fare applies, not currently used in POLARIS but is kept for consistency with the GTFS
origin	INTEGER	YES		Transit_Zones(origin)	Identifies an origin transit zone as seen in transit_zones table. For every unique origin-destination pair, create an entry.
destination	INTEGER	YES		Transit_Zones(destination)	Identifies a destination transit zone as seen in transit_zones table. For every unique origin-destination pair, create an entry.
contains	INTEGER	YES		Transit_Zones(contains)	Identifies the zones that a rider will enter while using a given fare class, not currently used in POLARIS but is kept for consistency with the GTFS

(* - Primary key)

The SQL statement for table and index creation is below.

create TABLE IF NOT EXISTS "Transit_Fare_Rules" (
    fare_id     INTEGER NOT NULL,
    route_id    INTEGER,
    origin      INTEGER,
    destination INTEGER,
    contains    INTEGER,

    FOREIGN KEY(fare_id) REFERENCES Transit_Fare_Attributes(fare_id) deferrable initially deferred,
    FOREIGN KEY(route_id) REFERENCES Transit_Routes(route_id) deferrable initially deferred,
    FOREIGN KEY(destination) REFERENCES Transit_Zones(transit_zone_id) deferrable initially deferred,
    FOREIGN KEY(origin) REFERENCES Transit_Zones(transit_zone_id) deferrable initially deferred,
    FOREIGN KEY(contains) REFERENCES Transit_Zones(transit_zone_id) deferrable initially deferred
);