@@ -123,6 +123,13 @@ SQL_Umsetzung: Superuser status → Create, Truncate, Trigger, Alter system,
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.
- Usage of controlled vocabularies 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.
- The Accreditation status of measurements are managed by a boolean column with controlled vocabulary used according to the following schema:
<details><summary>Example</summary>
- yes, fully: true
- no, but the values are of high reliability: false
- no: Exclude such data from data base
- unknown : NULL
</details>
- Check constraints for numeric values: Wherever possible numeric values are checked on insertion for their plausibility:
- areas, discharges, inhabitants... to be within a meaningful range, usually > 0 and smaller a maximum value.
