A new domain table, d_dataset was added to the database, containing the following columns:
- d_dataset_id - number. Automatically assigned primary key.
- dataset_guid - varchar2(255), not null, unique constraint. Automatically assigned GUID value, such as 348ecdc5-9c7c-4b72-81ae-bd8a5503bb75
- dataset_cd - varchar2(255), nullable. Allows some sort of meaningful standard code to be used to identify the dataset, if desired.
- dataset_ou_uid - number, not null. The organization database from which the entry originated.
- display_value - varhcar2(255), not null. Unique constraint.
- display_order - number, nullable. Automatically managed.
- ns_standard_ind - char, not null. Either Y/N.
Unique constraint: dataset_cd, dataset_ou_uid
There will be a standard set of centrally maintained entries for common data sets. Initially, there should be entries for:
- GBIF
- iNaturalist
- eBird
New Fields for Managed Feature Tables
The managed feature records (Source Feature, Element Occurrence, Managed Area, Conservation Site) should all be modified to have a new field that is populated from this domain table (d_dataset_id), as well as a corresponding global_id field.
- d_dataset_id - number, foreign key reference to d_dataset.d_dataset_id. Should be populated if the record originated from an external datasource. Usually optional, but required if global_id has a value.
- global_id - varchar2(255) - the record's identifier within the external datasource. Optional.
There should be a unique constraint across (xx_ou_uid, d_dataset_id, global_id).
Programs are allowed to adjust the display_value and display_order entries via SQL for any entries they wish, including entries which are centrally maintained. They should never alter dataset_cd after an entry has been created. Doing so will cause problems during data exchange, which relies upon the combination of dataset_guid, dataset_cd, and dataset_ou_uid to match these domain entries. Instead, create a new entry with the desired code, and use SQL to update any records using the previous dataset_id to use the replacement dataset_id.
To add new values to the d_dataset table for your jurisdiction, the recommendation is to enter new values through the Biotics user interface. Open a record page for an EO, SF, Site, or MA, open the search dialog, and add the desired values. Hit cancel to avoid saving the primary record if you don't need to use the newly created dataset entry.