From the Google GTFS reference guide, the 'Service_id' field of 'trips.txt' file is referenced from the calendar.txt or calendar_dates.txt file. I want to create a database for GTFS, so how I can add a foreign-key relationship here?
2
-
Anuraj, was I able to answer your question? – user55284 Nov 27 '15 at 8:33
4
Logically the service_id field should be defined as a foreign key in trips. However, this is made difficult by the fact it can reference a row in either calendar or calendar_dates. Normally foreign keys in a relational database define a relationship between exactly two tables, not three or more.
What I have done in the past is simply skip defining this relationship. It's not actually needed to run queries against the data, and if you trust (or have validated separately) the GTFS feed there's little value in having the DBMS enforce this additional constraint.
If you feel you must define a foreign-key relationship here, I'd suggest preprocessing the data you receive to convert the contents of calendar and calendar_dates to a form that can be used in this way. For instance, you might merge this data together into a single table and then use that as the foreign key's target.
Remember GTFS is intended only as a format for transmitting data, not as a means of storing it, so it's perfectly reasonable to use a different representation for your app internally if that better meets its needs.
For some examples of using GTFS calendar data in real queries, take a look at these questions on Stack Overflow:
