Currently, for tables like trips.txt we use calitp_itp_id, calitp_url_number, trip_id as a composite primary key.
This approach works okay for manual queries, but has three limitations:
it is cumbersome to write
it prohibits a bunch of metabase functionality (since metabase is limited to using a single primary key)
for a metric like daily validation codes, it's easy to trace back to current info (e.g. the url used to download the data today), but not info relevant to the specific date (e.g. the url used to download the data on THAT day).
Much of this is addressed by Kimball's dimensional modeling approach, where there are roughly two kinds of tables:
dimensional table: has a unique key column, and measurement columns (loosely the source data)
fact table: calculations over dimensional tables. Uses the keys to dimensional tables to link back to them.
Critically the keys in dimensional tables are unique over time, so e.g. if a trip entry were removed by a feed and the re-added, it would have separate keys. This allows us to easily link back to info as it existed for a calculation that was made.
machow
commented
3 years ago
Currently, for tables like
trips.txtwe usecalitp_itp_id,calitp_url_number,trip_idas a composite primary key.This approach works okay for manual queries, but has three limitations:
Much of this is addressed by Kimball's dimensional modeling approach, where there are roughly two kinds of tables:
Critically the keys in dimensional tables are unique over time, so e.g. if a trip entry were removed by a feed and the re-added, it would have separate keys. This allows us to easily link back to info as it existed for a calculation that was made.