@@ -128,12 +128,12 @@ The tethys_admin inherits all rights from the tethys_ca role.
The following procedures are implemented to ascertain data quality in the database:
- Traceability: Every record is automatically amended with the information who created it (column created_by) and at what time (column created_at) and who last modified it (column updated_by) and at which time (column updated_by). To document how pre-existing data were mapped on the controlled vocabulary of the database, for several attributed there is an additional text-column, where the original value should be stored. This column has the suffix \_reported. Deleted data from the tables containing the data are not deleted when user delete them, but marked as deleted and kept hidden from the ordinary user ("soft-deletion"). They can be restored by the administrators on request.
-**Traceability**: Every record is automatically amended with the information who created it (column created_by) and at what time (column created_at) and who last modified it (column updated_by) and at which time (column updated_by). To document how pre-existing data were mapped on the controlled vocabulary of the database, for several attributed there is an additional text-column, where the original value should be stored. This column has the suffix \_reported. Deleted data from the tables containing the data are not deleted when user delete them, but marked as deleted and kept hidden from the ordinary user ("soft-deletion"). They can be restored by the administrators on request.
Constraints: This database makes use of five user relevant constraints.
- Primary key constraints: Ensures that uniqueness as well as a not-null condition of a column entry or a combination of column entries are satisfied. As user are not supposed to import the id column as it is automatically generated, its only relevance to the users is, that the id column needs to get selected as custom key when deleting or updating data (compare issue #10).
- Not Null constraints: Ensures that a not-null condition of a column entry or a combination of column entries are satisfied. In the \__column_definition\_ view those columns are marked in the \_mandatory\_ column.
- Check constraint constraints: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
-**Primary key constraints**: Ensures that uniqueness as well as a not-null condition of a column entry or a combination of column entries are satisfied. As user are not supposed to import the id column as it is automatically generated, its only relevance to the users is, that the id column needs to get selected as custom key when deleting or updating data (compare issue #10).
-**Not Null constraints**: Ensures that a not-null condition of a column entry or a combination of column entries are satisfied. In the __column_definition_ view those columns are marked in the _mandatory_ column.
-**Check constraint**: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
<details>
<summary>Example1</summary>
...
...
@@ -150,9 +150,26 @@ Values are checked based on values in other columns: If a measurement is marked
SQL_Umsetzung: CONSTRAINT check_lod_available CHECK ((lod \\\\\\\> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))
</details>
- Unique key constraint: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
- Foreign key constraint: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
- Controlled vocabulary: Those are a special version of foreign keys, here used for qualitative attributes: Whenever a qualitative attribute is recorded, controlled vocabularies (a list of allowed values for that column) are used. These assure, that the same thing is always called the same (and without typos) and no further data cleaning is necessary before data evaluation. Controlled vocabularies are stored in separate tables. These tables can not be changed by the ordinary user, but only by admins. Deletion in controlled vocabularies are not recorded in the database itself (no soft delete mechanism), here the admin is responsible to keep a backup. In the \__column_definition\_ view those columns are marked in the \__controlled_vocabulary\_ and. Possible entries for those columns can be seen in the \__controlled_vocabularies\_ view.
-**Unique key constraint**: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
A unique key, in this case an identifier consits of several information of its row
identifier_analytical_method: Identifier of an analytical laboratory method defined in the table determ.analytical_methods. A laboratory method in best case is defined by a Standard (ISO or other) and consists of sample preparation and sample analysis techniques with appropriate measures for quality assurance and a validation procedure.
</details>
-**Foreign key constraint**: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
<details><summary>Example</summary>
A foreign key references one or in this example two columns of another table case sensitive.
SQL_Umsetzung: ALTER TABLE hidden.samples ADD FOREIGN KEY (identifier_sampling_site, sampling_site_compartment) REFERENCES hidden.sampling_sites (identifier_sampling_site, compartment) ON UPDATE RESTRICT ON DELETE RESTRICT;
</details>
-**Controlled vocabulary**: Those are a special version of foreign keys, here used for qualitative attributes: Whenever a qualitative attribute is recorded, controlled vocabularies (a list of allowed values for that column) are used. These assure, that the same thing is always called the same (and without typos) and no further data cleaning is necessary before data evaluation. Controlled vocabularies are stored in separate tables. These tables can not be changed by the ordinary user, but only by admins. Deletion in controlled vocabularies are not recorded in the database itself (no soft delete mechanism), here the admin is responsible to keep a backup. In the \__column_definition\_ view those columns are marked in the \__controlled_vocabulary\_ and. Possible entries for those columns can be seen in the \__controlled_vocabularies\_ view.
