Build a guideline for checking and fixing input

[ValentineHerr]

This would likely be a standalone markdown (so we can share link) with imbedded code for suggestions on how to check the data before bringing it into the app. The file will be visualizable in the app.

[gabrielareto]

Stuart mentioned this as a useful ref: https://bien.nceas.ucsb.edu/bien/wp-content/uploads/2013/01/Wiser_etal_2011_VegX.pdf

[gabrielareto]

I add Suzanne's input here:

-- Make sure that tags, stemtags, and quadrats are character fields, so as not to lose leading zeros and tags/stemtags/quadrats with letters or dashes.

• Check to make sure all measurement codes are valid. The delimiter ";" is used to separate codes in our case.

• MySQL accepts dates in the format yyyy-mm-dd. Unfortunately, if you ask for dates in the regular format, they may come as dd-mm-yyyy and mm-dd-yyyy in the same file.

Make sure the dates fall within the census range.

• Make sure all species codes are valid.

• Make sure all quadrats are valid.

• Check for duplicate Tag+Stemtags (i.e. in different quadrats or have different species codes).

• Check for diameters and homs outside the permitted range.

• Flag records with no diameter measurement and no codes (explaining whether tree is broken, dead, fallen, etc.).

  • Our methodology specifies that dead trees are not measured (dbh=NULL). Some sites do measure dead trees. So we flag those records where there is a code='dead' and there is a diameter measurement.

• For every multiple-stemmed tree, make sure there is not more than one "main" stem code, if the plot is using this column (primary/secondary).

• Make sure that all the multiple stems of the same tree have the same species code

• Make sure that all the multiple stems of the same tree belong to the same quadrat, or at least to neighboring quadrats.

• Make sure the coordinates are valid within the quadrat and within the plot.

• If more than one census:

1. flag trees where the growth rates are too high or are negative (diameter shrunk).
2. make sure all trees that are alive are accounted for in the next census.

[gabrielareto]

(some notes that I took about proper relational db design)

• everything is in tables. For example, we cannot store the census identifier or temporal information in file names, like in census1.csv, census2.csv, census3.csv, etc.
• there must be a primary key that uniquely identify the rows in a table
• there must be unique column names within each table
• primary and foreign key fields are explicitly designated as such
• row and column meaning cannot depend on row or column order
• there is only one value per cell (atomicity)
• one row represents one instance of a particular entity
• different rows cannot contain data on different entities
• tables related with foreign keys
• if the foreign key calls a row from a different table that row must exist. E.g. don't call a plot_id in an individual-level table if that plot_id does not appear in the plot-level table.
• remove redundancy in storage. Example: individual-level table with voucher_id and species that is the repetition of a voucher-level table with voucher_id and species. Check congruence before removing!
• separated columns for data that can or could be used separatedly (to sort, filter, etc.). E.g. genus
• 2NF: every column in a table depends on whole primary key
• 3NF: no columns depend on anything but the primary key
• many-to-many relationships between keys are stored in separated tables. E.g. "abundance" contains species_name and plot_id in a separated table, not in the species-level table neither the plot-level table.
• derived (calculated) fields are not stored
• foreign keys and the primary keys they represent have identical names. E.g. the species name is called species_name in all tables and not "sp" in the individual-level table and "latin_binomial" somewhere else.

[gabrielareto]

some other notes about relational databases. normal-forms-etc.docx