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 and station_id. This relates the motion to the event that produced the ground shaking and the station that recorded it. .. _schema: https://www.uclageo.com/gm_database/api/index.php/schema 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 | +==================+===========================+=============================+============================+======================+ | basin_model | basin_site | finite_fault | collection_motion | acc | + + + + + + | (basin_model_id) | (basin_site_id) | (ff_id) | (collection_motion_id) | (acc_id) | + + + + + + | | [basin_model_id, site_id] | [event_id] | [collection_id, motion_id] | [ts_id] | +------------------+---------------------------+-----------------------------+----------------------------+----------------------+ | collection | event | motion | finite_fault_seg | | + + + + + + | (collection_id) | (event_id) | (motion_id) | (ff_seg_id) | | + + + + + + | | [event_type_id] | [event_id, station_id] | [ff_id] | | +------------------+---------------------------+-----------------------------+----------------------------+----------------------+ | component | station | station_ssn | fourier_spectra | | + + + + + + | (component_id) | (station_id) | (station_ssn_id) | (FAS_id) | | + + + + + + | | [site_id, network_id] | [station_id, collection_id] | [motion_id, component_id] | | +------------------+---------------------------+-----------------------------+----------------------------+----------------------+ | event_type | | | intensity_measure | | + + + + + + | (event_type_id) | | | (im_id) | | + + + + + + | | | | [motion_id, component_id] | | +------------------+---------------------------+-----------------------------+----------------------------+----------------------+ | network | | | resp_spectra | | + + + + + + | (network_id) | | | (Sa_id) | | + + + + + + | | | | [motion_id, component_id] | | +------------------+---------------------------+-----------------------------+----------------------------+----------------------+ | site | | | time_series | | + + + + + + | (site_id) | | | (ts_id) | | + + + + + + | | | | [motion_id, component_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: https://www.uclageo.com/gm_database/api/index.php/flatfile?format=html&tables=intensity_measure,motion,component,event,station,site,network,event_type_id Continuity of the key chain can be verified by working from right-to-left starting with the intensity_measure table. 1. The intensity measure table has motion_id and component_id as foreign keys, so the motion and component tables are included. 2. The motion table has event_id and station_id as foreign keys, so the event and station tables are included. 3. The station table has site_id and network_id as foreign keys, so the site and network tables are included. 4. 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: https://www.uclageo.com/gm_database/api/index.php/flatfile?format=html&tables=intensity_measure,network The intensity measure table is at Level 3 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: 1. Add the motion and component tables since the foreign keys of the intensity_measure table are motion_id and component_id. 2. Add the event and station tables since the foreign keys of the motion table are event_id and station_id. Note that component is a Level 0 table, so no further action is needed there. 3. Add the event_type, site, and network tables since event_type_id is a foreign key in the event table, and site_id and network_id are foreign keys in the station 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.