My initial analysis is we need the following tables:
observation. The primary scientific record extracted from the literature. Will contain:
plant
compound List
percentages
plant parts
location
bibliographic record
conditions
(I have probably missed some). This is through secondary keys/links to the normalized tables below
plant. This holds the identity of the plant (binomial name, other plant identifiers)
compound. This holds the identity of the compound (chemical name, formula, other compound identifiers)
location (name of place, coordinates or link to Wikidata)
plant parts
bibliographic record
conditions
The new table is Observations
This is the key data in the database. An article could contain several observations (e.g.
each table may be an observation). Therefore the DOI is not a unique identifier.
This is a relatively simple structure, except for the compounds/pecentages which are an n:1 mapping. We may need an abundance table which links observations to multiple percentages.
My initial analysis is we need the following tables:
The new table is Observations This is the key data in the database. An article could contain several observations (e.g. each table may be an observation). Therefore the DOI is not a unique identifier.
This is a relatively simple structure, except for the compounds/pecentages which are an n:1 mapping. We may need an abundance table which links observations to multiple percentages.