Enable variable to be categorical

[dlebauer]

Note: this is open for discussion, currently on hold pending feedback from people who will collect categorical data.

Some traits are categorical - these are commonly used by breeders. The species table imported from USDA Plants has a number of categorical traits (e.g. 'PropagatedBySeed').

For the TERRAREF program, we need to allow categorical variables, but have decided to track these in traits in order to capture the who / where / when metadata (this is not captured in the species table). Categorical traits include some traits that could be quantified, but are not quantified in practice, such as 'seed color', 'maturity class' etc.

Proposed solution:

To support the collection of such data we will create two new fields in the variables table:

1. 'categorical' of type boolean
2. 'options' of type array
   • arrays will have fields value, name, definition

When returning data, we can use lookup. As an example, the variable maturity class would be recorded as

field	value
id	999
description	maturity class
units	NULL
notes	Maximum rate of RuBP regeneration.
name	maturity_class
max	0
min	1
type	trait
categorical	TRUE
options

value	option_name	definition
0	early	senesces in < 100 days after planting
1	late	senesces in > 100 days after planting

Other options

1. add new columns to cultivars for each characteristic (this is not normalized, requires migrations)
2. add a cultivars_characteristics and characteristics table (to prevent > 90% sparse table like species)
3. figure out how to convert all categorical traits to numeric (e.g. in above example, use 'senescence' as an observation, days after planting computed from observation date - planting date.
   • categories such as 'maturity group' can be computed 'on the fly'
4. (can be combined with above) use BMS to store categorical variables.

would appreciate feedback from @nfahlgren, @terraref/standards-committee

[ashiklom]

So, related to the PEcAn discussion on 9/28/2016, I think that there should be a distinction between individual-specific categorical traits (e.g. color, health, etc.) and species-specific categorical traits (@mdietze suggested the term "attributes"). I think individual-specific categorical traits should go in the traits and variables table, as @dlebauer suggested above, but I think species-specific attributes should go in their own table.

Here's a proposed revision to the schema. In a nutshell, the species table should only have the information needed to unambiguously identify a species, and any additional species attributes should go in the attributes table.

TABLE species(
id INTEGER,
USDA_code TEXT,
scientificname TEXT,
commonname TEXT,
genus TEXT,
species TEXT,
variety TEXT,
USDA_code_synonyms TEXT,    # For searching via `%LIKE%` commands
scientificname_synonyms TEXT,
commonname_synonyms TEXT)

TABLE attributes(
id INTEGER,
species_id INTEGER,
variable_id INTEGER,   # Will need to add all the attributes to the variables table
value TEXT,
comments TEXT)

On a related note, it would be good to revise the variables table that specifies what kind of variable something is -- i.e. to make it easy to quickly isolate attribute names from the variables table. As far as I know, there is no such column right now.

Some example attributes (inspired by data I found in TRY):

• Woodiness -- woody vs. herbaceous
• Leaf phenology -- deciduous vs. evergreen
• Photosynthetic type -- C3, C4, CAM
• Growth form -- see USDA growth forms)
• Leaf type -- broadleaved, evergreen
• Leaf morphology -- see Wikipedia
• Dispersal vector -- animal, wind
• Pollination

[ashiklom]

A grey area in here is species phylogeny. I would argue for sticking it in the attributes table with family, clade, etc. as their own attributes, but there could also be legitimate reasons for keeping phylogenetic information in the species table.

[dlebauer]

@ashiklom re "revise the variables table that specifies what kind of variable something is" there is a 'type' field in the variables table