search

EDIorg / ecocomDP

A dataset design pattern and R package for ecological community data.
https://ediorg.github.io/ecocomDP/
Other
32 stars 13 forks source link

Table event #4

Closed mobb closed 7 years ago

mobb commented 7 years ago

Table event doesn’t have a primary key because one event can have several variable and values pairs Needs a record_id or something along those lines as primary key event_id needs an index to be able to use it as foreign key in observation

mobb commented 7 years ago

Also needs a units field, E.g., some events will be similar to observations, like temperature (var) was (value), measured in (units).

mobb commented 7 years ago

things that go in this table: depth of a sample temperature of that example based on https://portal.edirepository.org/nis/metadataviewer?packageid=knb-lter-ntl.344.2 example_event_onerecord.txt

mobb commented 7 years ago

Recommendation: offsets (up or down) from the elevation are measurements that belong in the event table.

mobb commented 7 years ago

Reopening because I still have a problem with this table. Event table is essentially an observation_ancillary, and is analogous to taxon_ancillary and samp_loc_ancillary because it is a) organized as key-value-unit and b) a secondary table, c) holds additional info about the observation.

So I think that the field that should link back to observation is the event_id, not the event_record_id. a single observation needs to link to the entire event (multiple records).

and event_record_id is just the PK for the table, the same as taxon_ancillary and samp_loc_ancillary have a PK, but that is not the field that links to the parent.

we could consider changing the name of this table to obs_ancillary, to make that more obvious.

mobb commented 7 years ago

Yup. had the reference wrong. we still could consider renaming, though.