Key logic
If more than one table is specified in the “tables” query string, those tables must be joined together to form the flatfile. Tables are joined based on keys. A primary key is a field that uniquely identifies each record in a table. In the schema a primary key is identified by Key = PRI. A foreign key is a field in one table that specifies a relationship with a key in a separate table. In the schema a foreign key is identified by Key = MUL. For example, the motion table’s primary key is motion_id, and foreign keys are event_id, station_id, and user_id. This relates the motion to the event that produced the ground shaking, the station that recorded it, and the user that uploaded the data.
Figure 1. Diagram of simplified schema including primary (gold) and foreign (silver) key relationships; event tables colored red; station-site tables colored green; motion path table colored blue; ground motion data tables colored yellow; and auxiliary information tables colored gray.
Key chain
Selecting tables to include in a query therefore depends on the chain of primary key / foreign key constraints present in the database. The table below is a heirarchical representation of the foreign key / primary key constraints in our database. Level 0 contains tables without a foreign key, Level 1 contains tables whose foreign keys correspond to primary keys in Level 0, Level 2 contains tables whose foreign keys are contained in Level 1 or lower, and so forth.
(primary key) [foreign key]
| Level 0 | Level 1 | Level 2 | Level 3 | Level 4 | Level 5 |
|---|---|---|---|---|---|
basin_model (basin_model_id) |
event (event_id) [event_type_id] |
aftershock_mainshock (aftershock_mainshock_id) [event_id] |
finite_fault_kinematic_parameter (finite_fault_kinematic_parameter_id) [finite_fault_id] |
collection_motion (collection_motion_id) [collection_id, motion_id] |
time_series_data (time_series_data_id) [time_series_metadata_id] |
collection (collection_id) |
site (site_id) [vs30_code_id] |
basin_site (basin_site_id) [basin_model_id, site_id] |
finite_fault_segment (finite_fault_segment_id) [finite_fault_id] |
fourier_spectra (fourier_spectra_id) [motion_id] |
|
event_type (event_type_id) |
event_eqid (event_eqid_id) [collection_id, event_id] |
motion (motion_id) [event_id, station_id, user_id] |
intensity_measure (intensity_measure_id) [motion_id] |
||
geometry (geometry_id) |
finite_fault (finite_fault_id) [event_id] |
station_ssn (station_ssn_id) [collection_id, station_id] |
response_spectra (response_spectra_id) [motion_id] |
||
network (network_id) |
station (station_id) [site_id, network_id] |
time_series_metadata (time_series_metadata_id) [motion_id] |
|||
user (user_id) |
|||||
vs30_code (vs30_code_id) |
|||||
Continuous key chains
We suggest the best practice in forming your query string is to make sure your table list forms a continuous key chain such that there are no gaps between table levels. For example, the following query contains a continuous key chain:
Continuity of the key chain can be verified by working from right-to-left starting with the intensity_measure table.
- The intensity measure table has motion_id as a foreign key, so the motion table is included.
- The motion table has event_id, station_id, and user_id as foreign keys, so the event, station, and user tables are included.
- The station table has site_id and network_id as foreign keys, so the site and network tables are included.
- The site table has vs30_code_id as a foreign key, so the vs30_code table is included.
- The event table has event_type_id as a foreign key, so the event_type table is included.
Broken key chains
Although it is best practice to use a continuous key chain, the API will work if users specify a broken key chain. For example, let’s say a user enters the following:
The intensity measure table is at Level 4 and the network table is at Level 0, so there is a gap in the key chain. The tables must be connected in order to form a valid SQL query. So we can either tell the user to reformat their query so they have a continuous key chain, or we can do our best to form a key chain for them. We adopt the latter approach. In this case, we start at the highest level and add tables from lower levels until the key chain is complete. The steps in this case would be:
- Add the motion table since the foreign key of the intensity_measure table is motion_id.
- Add the event, station, and user tables since the foreign keys of the motion table are event_id, station_id, and user_id.
- Add the event_type, network, site, and vs30_code tables since event_type_id is a foreign key in the event table, network_id and site_id are foreign keys in the station table, and vs30_code_id is a foreign key in the site table.
Furthermore, we will return fields from all tables involved in the query to make clear that we have joined tables together to complete the key chain. The exception is if the user specifies the “fields” query string parameter, in which case only those fields are returned.