Skip to Content.
Sympa Menu

pcplantdb - Re: [pcplantdb] attributes

pcplantdb@lists.ibiblio.org

Subject: pcplantdb

List archive

Chronological Thread  
  • From: Richard Morris <webmaster@pfaf.org>
  • To: pcplantdb@lists.ibiblio.org
  • Subject: Re: [pcplantdb] attributes
  • Date: Tue, 24 Aug 2004 14:31:16 +0100

Chad Knepp wrote:

Ok, let's talk about objects:

Excelent.

Here are some of the things I think should be in a plant:

Name sorts of thing:

o Family (word, not null)

Posibly also Author for family,
and common name for family (extractable from UDSA).
Higher order taxa might also be useful, for instance
the Monocotyledon/Dicotyledons division is useful info.

o Genus (word, not null)
o Species (word, not null)
o Subspecies (word)
o Variety (word)

Also need to represent hybrids eg Elaeagnus x ebbingei,
cultivars and ocasionally forma and sub varieties.
Exposing the full scientific name posibly by a method
could make things easy display wise.

o Author (word)

o Synonyms (list of family,genus,species, etc...)

Probably a separate table or class.

> o Common names (list of words, not null)

To common names i'd like to attach a language key and posibly
also a region key.

Cultural details

o Hardy to tempature (number)


o Growth habit (list of words)

Might break this into two fields:
Life cycle:
Annual/Bi-annual/Pernenial (posible for more than one)
Habit: Tree, Shrub, Forb, Climber, ...

UDSA uses this.
There are a few curiosities in pfaf dataset, is Bamboo really a habit?
If we have bamboo sholud we also include other major plant groupings
grass/sedges/rushes, Palms, Suclient, many of these groups
corespond well to families or higher taxa.

o Max height (number)

UDSA occasionally have height at 20 years.

What happens when someone want to change the height?

o Max width (number)
o Preferred
Soil (words)
Ph (number)

Actually a range low-high. How we translate the PFAF Acid/Neutral/Base
clasification to a number could be tricky.

Fertility (word)
Soil moisture (word)
Annual moisture (number)
Shade (word)
o Tolerates adverse (list of words)
o Flower type (word)
o Cultivations details (text)
o Propagation details (text)
o Cultivars (list of cultivar objects)

Uses:

o Use details (text)
o Hazards (text)
Must also include invasive plants (with region).
Lots of people have written to us saying how can you recomend
this plant when its an invasive weed where I live.


How do people feel about preserving reference information (links to
books)... and/or references to other sites?
Absolutly. References are v important for checking acuracy of data.
Could posibly put into a separate page so its there but not cluttering
the display.

Ayway, this is my bare minimum and I probably forgot stuff I would
include in the minimum.

Other stuff:

Identification Info:
Flower colour, leaf shape, ...
could be tricky to do well. Could just leave as text field for now.

Pests & diseases;
One example of relationships.
Might want to have negative relationships.
"resistant to honey fungus"

Food plants for:
relationships to animals

Polinated by:

Yields:
UDSA, has some good fields for this sort of stuff.

Description:
A short paragraph giving a overview of plant.

Calander:
Rec time to sow, plant out, ...
could be hard to make this global.


I'm attaching the database schema from Eden and the usda data so you
can get an idea for other things to include. If they looked whacked
in your browser try changing your font to fixed-width.

Some notes below.


Database: eden Table: botanical_name Rows: 7396 Wildcard: %
+------------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default |
Extra | Privileges |
+------------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 |
| select |
| legacy_pfaf_latin_name | varchar(100) | | | |
| select |
| family | varchar(50) | | MUL | |
| select |
| genus | varchar(50) | | | |
| select |
| species | varchar(50) | | | |
| select |
| ssp | varchar(50) | YES | | |
| select |
| common_name | varchar(50) | YES | MUL | |
| select |
+------------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: cache Rows: 7 Wildcard: %
+---------+---------------+------+-----+---------+-------+----------------------+
| Field | Type | Null | Key | Default | Extra | Privileges
|
+---------+---------------+------+-----+---------+-------+----------------------+
| search | varchar(255) | | PRI | | |
select,insert,update |
| time | timestamp(14) | YES | | | |
select,insert,update |
| counter | int(11) | | | 1 | |
select,insert,update |
| page | text | | | | |
select,insert,update |
+---------+---------------+------+-----+---------+-------+----------------------+
Database: eden Table: composition Rows: 146 Wildcard: %
+---------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | |
select |
| part_of_plant | varchar(50) | | | Fruit | |
select |
| dry_or_fresh_weight | char(1) | | | F | |
select |
| water | float | YES | | | |
select |
| calories | float | YES | | | |
select |
| protein | float | YES | | | |
select |
| fat | float | YES | | | |
select |
| carbohydrate | float | YES | | | |
select |
| fibre | float | YES | | | |
select |
| ash | float | YES | | | |
select |
| calcium | float | YES | | | |
select |
| phosphorus | float | YES | | | |
select |
| iron | float | YES | | | |
select |
| magnesium | float | YES | | | |
select |
| sodium | float | YES | | | |
select |
| potassium | float | YES | | | |
select |
| zinc | float | YES | | | |
select |
| vitamin_a | float | YES | | | |
select |
| thiamine | float | YES | | | |
select |
| riboflavin | float | YES | | | |
select |
| niacin | float | YES | | | |
select |
| vitamin_c | float | YES | | | |
select |
| vitamin_b6 | float | YES | | | |
select |
| source | tinytext | YES | | | |
select |
| notes | tinytext | YES | | | |
select |
+---------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: cultivars Rows: 1902 Wildcard: %
+-------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | |
select |
| cultivar | tinytext | | | | |
select |
| notes_on_cultivar | text | YES | MUL | | |
select |
| synonyms | tinytext | YES | | | |
select |
+-------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: cultural_notes Rows: 7396 Wildcard: %
+---------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | |
select |
| cultivation_details | text | | MUL | | |
select |
| propagation_details | text | | MUL | | |
select |
| known_hazards | tinytext | | MUL | | |
select |
+---------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: culture Rows: 7396 Wildcard: %
+---------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | |
select |
| hardyness | tinyint(4) | | | 11 | |
select |
| range | tinytext | | | | |
select |
| habitat | tinytext | | | | |
select |
| habit | varchar(50) | | | | |
select |
| deciduous_evergreen | char(1) | | | D | |
select |
| height_centimeters | smallint(5) unsigned | | | 100 | |
select |
| width_centimeters | smallint(5) unsigned | | | 50 | |
select |
| growth_rate | char(1) | | | M | |
select |
| nitrogen_fixer | char(1) | | | N | |
select |
| supports_wildlife | char(1) | | | N | |
select |
| months_in_leaf | varchar(50) | YES | | | |
select |

Rather awkward field to use. Could have list of months.

| months_in_flower | varchar(50) | YES | | | |
select |
| flower_type | char(1) | | | H | |
select |
| pollinators | varchar(128) | YES | | | |
select |
| self_fertile | char(1) | | | N | |
select |
| months_seed_ripe | varchar(50) | YES | | | |
select |
| scented | char(1) | | | N | |
select |
| soil | char(3) | | | LMH | |
select |
| heavy_clay | char(1) | | | N | |
select |
| poor_soil | char(1) | | | N | |
select |
| ph | char(3) | | | ANB | |
select |
| acid | char(1) | | | N | |
select |
| alkaline | char(1) | | | N | |
select |
| saline | char(1) | | | N | |
select |
| moisture | char(3) | | | M | |
select |
| drought | char(1) | | | N | |
select |
| shade | char(3) | | | SN | |
select |
| wind | char(1) | | | N | |
select |
| pollution | char(1) | | | N | |
select |
| frost_tender | char(1) | | | Y | |
select |
+---------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: hybrids Rows: 98 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | | select |
+-------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: plant_locations Rows: 7384 Wildcard: %
+-----------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-----------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | |
select |
| woodland_garden | char(1) | | | N | |
select |
| canopy | char(1) | | | N | |
select |
| secondary | char(1) | | | N | |
select |
| sunny_edge | char(1) | | | N | |
select |
| dappled_shade | char(1) | | | N | |
select |
| shady_edge | char(1) | | | N | |
select |
| deep_shade | char(1) | | | N | |
select |
| other_habitats | char(1) | | | N | |
select |
| cultivated_beds | char(1) | | | N | |
select |
| ground_cover | char(1) | | | N | |
select |
| lawn | char(1) | | | N | |
select |
| meadow | char(1) | | | N | |
select |
| hedge | char(1) | | | N | |
select |
| hedgerow | char(1) | | | N | |
select |
| pond | char(1) | | | N | |
select |
| bog_garden | char(1) | | | N | |
select |
| walls | char(1) | | | N | |
select |
+-----------------+----------------------+------+-----+---------+-------+------------+

Possibly could do some work on these habitats. Rather narrow
compared with a great variety of habitats in the habitats text field.

Database: eden Table: range Rows: 7394 Wildcard: %
+---------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | |
select |
| britain | tinytext | YES | | | |
select |
| europe | tinytext | YES | | | |
select |
| mediterranean | tinytext | YES | | | |
select |
| w_asia | tinytext | YES | | | |
select |
| e_asia | tinytext | YES | | | |
select |
| n_america | tinytext | YES | | | |
select |
| s_america | tinytext | YES | | | |
select |
| africa | tinytext | YES | | | |
select |
| australasia | tinytext | YES | | | |
select |
| other | tinytext | YES | | | |
select |
+---------------+----------------------+------+-----+---------+-------+------------+

This is very UK centric in PFAF data. Whole of USA & canada
is just n_america.

Might want to divide this up into "native in ...."
and "grows in ...."

Probably also want country, state/region....

Database: eden Table: rating Rows: 7396 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | | select |
| pfaf | tinyint(3) unsigned | | | 1 | | select |
+-------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: references_index Rows: 70607 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | | select |
| book | smallint(5) unsigned | | | 0 | | select |
+-------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: references_list Rows: 270 Wildcard: %
+------------------+-------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+------------------+-------------+------+-----+---------+-------+------------+
| number | smallint(6) | | PRI | 0 | | select |
| title | tinytext | | | | | select |
| author | varchar(50) | YES | | | | select |
| comments | tinytext | | | | | select |
| publisher | varchar(50) | YES | | | | select |
| publication_date | smallint(6) | YES | | | | select |
| isbn | varchar(50) | YES | | | | select |
+------------------+-------------+------+-----+---------+-------+------------+
Database: eden Table: scented_plants Rows: 1169 Wildcard: %
+-------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | | select
|
| plant_part | tinytext | YES | | | | select
|
| fresh | tinyint(4) | YES | | | | select
|
| crushed | tinyint(4) | YES | | | | select
|
| dried | tinyint(4) | YES | | | | select
|
| scent_class | tinytext | YES | | | | select
|
| notes | tinytext | YES | | | | select
|
+-------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: synonyms Rows: 4579 Wildcard: %
+------------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default |
Extra | Privileges |
+------------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 |
| select |
| legacy_pfaf_synonym | varchar(100) | YES | | |
| select |
| author | tinytext | YES | | |
| select |
| legacy_pfaf_latin_name | varchar(100) | YES | | |
| select |
| genus | varchar(50) | YES | | |
| select |
| species | varchar(50) | YES | | |
| select |
| ssp | varchar(50) | YES | | |
| select |
+------------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: uses_glossary Rows: 271 Wildcard: %
+------------+--------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+------------+--------------+------+-----+---------+-------+------------+
| uses | varchar(100) | | MUL | | | select |
| definition | tinytext | | | | | select |
+------------+--------------+------+-----+---------+-------+------------+
Database: eden Table: uses_index Rows: 34006 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | MUL | 0 | | select |
| uses | varchar(100) | | MUL | | | select |
+-------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: uses_text Rows: 7396 Wildcard: %
+-----------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-----------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | | select
|
| edible | text | YES | MUL | | | select
|
| medicinal | text | YES | | | | select
|
| other | text | YES | | | | select
|
+-----------+----------------------+------+-----+---------+-------+------------+

Database: usda
+------------------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default
| Extra |
+------------------------------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | | | 0
| |
| symbol_key | varchar(100) | YES | |
| |
| synonym_symbol_key | varchar(100) | YES | |
| |
| symbol | varchar(100) | YES | |
| |
| xgenus | varchar(100) | YES | |
| |
| x_genus | varchar(100) | YES | |
| |
| xspecies | varchar(100) | YES | |
| |
| x_species | varchar(100) | YES | |
| |
| ssp | varchar(100) | YES | |
| |
| subspecies | varchar(100) | YES | |
| |
| var | varchar(100) | YES | |
| |
| variety | varchar(100) | YES | |
| |
| forma | varchar(100) | YES | |
| |
| genera_author | varchar(100) | YES | |
| |
| bi_author | varchar(100) | YES | |
| |
| tri_author | varchar(100) | YES | |
| |
| quad_author | varchar(100) | YES | |
| |
| scientific_name | varchar(100) | YES | |
| |
| common_name | varchar(100) | YES | |
| |
| us_na_plant | varchar(100) | YES | |
| |
| category | varchar(100) | YES | |
| |
| tis_tsn | varchar(100) | YES | |
| |
| genus | varchar(100) | YES | |
| |
| family | varchar(100) | YES | |
| |
| order | varchar(100) | YES | |
| |
| class | varchar(100) | YES | |
| |
| division | varchar(100) | YES | |
| |
| growth_habit | varchar(100) | YES | |
| |
| duration | varchar(100) | YES | |
| |
| us_native | varchar(100) | YES | |
| |
| federal_TE | varchar(100) | YES | |
| |
| federal_TE_name | varchar(100) | YES | |
| |
| state_TE | varchar(100) | YES | |
| |
| state_TE_name | varchar(100) | YES | |
| |
| national_wetland | varchar(100) | YES | |
| |
| regional_wetland | varchar(100) | YES | |
| |
| photo | varchar(100) | YES | |
| |
| drawing | varchar(100) | YES | |
| |
| fact_sheet_pdf | varchar(100) | YES | |
| |
| fact_sheet_doc | varchar(100) | YES | |
| |
| plant_guide_pdf | varchar(100) | YES | |
| |
| plant_guide_doc | varchar(100) | YES | |
| |
| charactersitics | varchar(100) | YES | |
| |
| cultivar_name | varchar(100) | YES | |
| |
| active_growth_period | varchar(100) | YES | |
| |
| after_harvest_regrowth_rate | varchar(100) | YES | |
| |
| alleopath | varchar(100) | YES | |
| |
| bloat | varchar(100) | YES | |
| |
| CN_ratio | varchar(100) | YES | |
| |
| coppice_potential | varchar(100) | YES | |
| |
| fall_conspicuous | varchar(100) | YES | |
| |
| fire_resistance | varchar(100) | YES | |
| |
| flower_color | varchar(100) | YES | |
| |
| flower_conspicous | varchar(100) | YES | |
| |
| foliage_color | varchar(100) | YES | |
| |
| foliage_porosity_summer | varchar(100) | YES | |
| |
| foliage_porosit_winter | varchar(100) | YES | |
| |
| foliage_texture | varchar(100) | YES | |
| |
| fruit_seed_color | varchar(100) | YES | |
| |
| fruit_seed_conspicuos | varchar(100) | YES | |
| |
| growtn_form | varchar(100) | YES | |
| |
| growth_rate | varchar(100) | YES | |
| |
| height_20_years_feet | varchar(100) | YES | |
| |

interesting!

| height_max_feet | varchar(100) | YES | |
| |
| leaf_retention | varchar(100) | YES | |
| |
| lifespan | varchar(100) | YES | |
| |
| low_growing_grass | varchar(100) | YES | |
| |
| nitrogen_fixer | varchar(100) | YES | |
| |
| resprout_ability | varchar(100) | YES | |
| |
| shape_and_orientation | varchar(100) | YES | |
| |
| toxicity | varchar(100) | YES | |
| |
| course_soil | varchar(100) | YES | |
| |
| fine_soil | varchar(100) | YES | |
| |
| medium_soil | varchar(100) | YES | |
| |
| anaerobic_tolerance | varchar(100) | YES | |
| |
| caco3_tolerance | varchar(100) | YES | |
| |
| cold_stratification_required | varchar(100) | YES | |
| |
| drought_tolerance | varchar(100) | YES | |
| |
| fertility_requirements | varchar(100) | YES | |
| |
| fire_tolerance | varchar(100) | YES | |
| |
| frost_free_days_minimum | varchar(100) | YES | |
| |
| hedge_tolerance | varchar(100) | YES | |
| |
| moisture_use | varchar(100) | YES | |
| |
| ph_min | varchar(100) | YES | |
| |
| ph_max | varchar(100) | YES | |
| |
| plant_density_min | varchar(100) | YES | |
| |
| plant_density_max | varchar(100) | YES | |
| |

This could be good growing info.

| precipitation_min | varchar(100) | YES | |
| |
| precipitation_max | varchar(100) | YES | |
| |
| root_depth_min | varchar(100) | YES | |
| |
| saline_tolerance | varchar(100) | YES | |
| |
| shade_tolerance | varchar(100) | YES | |
| |
| temp_min_F | varchar(100) | YES | |
| |
| bloom_period | varchar(100) | YES | |
| |
| commercial_available | varchar(100) | YES | |
| |
| fruit_seed_abundance | varchar(100) | YES | |
| |
| fruit_seed_begin | varchar(100) | YES | |
| |
| fruit_seed_end | varchar(100) | YES | |
| |
| fruit_seed_persist | varchar(100) | YES | |
| |

| bare_root_propagate | varchar(100) | YES | |
| |
| bulb_propagate | varchar(100) | YES | |
| |
| container_propagate | varchar(100) | YES | |
| |
| corm_propagate | varchar(100) | YES | |
| |
| cutting_propagate | varchar(100) | YES | |
| |
| seed_propagate | varchar(100) | YES | |
| |
| sod_propagate | varchar(100) | YES | |
| |
| sprig_propagate | varchar(100) | YES | |
| |
| tuber_propagate | varchar(100) | YES | |
| |
| seeds_pound | varchar(100) | YES | |
| |
| seed_spread_rate | varchar(100) | YES | |
| |
| seedling_vigor | varchar(100) | YES | |
| |
| small_grain | varchar(100) | YES | |
| |
| vegatative_spread_rate | varchar(100) | YES | |
| |

| beery_nut_seed_product | varchar(100) | YES | |
| |
| christmas_product | varchar(100) | YES | |
| |
| fodder_product | varchar(100) | YES | |
| |
| fuelwood_product | varchar(100) | YES | |
| |
| lumber_product | varchar(100) | YES | |
| |
| navel_store_product | varchar(100) | YES | |
| |
| nursery_stock_product | varchar(100) | YES | |
| |
| browse_animal | varchar(100) | YES | |
| |
| graze_animal | varchar(100) | YES | |
| |
| human_edible | varchar(100) | YES | |
| |
| post_product | varchar(100) | YES | |
| |
| protien_potential | varchar(100) | YES | |
| |
| pulpwood_product | varchar(100) | YES | |
| |
| veneer_product | varchar(100) | YES | |
| |

Some interesting uses. Lumber/fulewood is better than PFAF's Wood.

We also got to think about how we represent edits to the
data. We seem ot have three sorts of element.

Text fields:
Keywords:
Numeric data:

might need different stratergies for each.
But enough for now.

Rich

--
Plants for a Future: 7000 useful plants
Web: http://www.pfaf.org/ same as http://www.comp.leeds.ac.uk/pfaf/
Post: 1 Lerryn View, Lerryn, Lostwithiel, Cornwall, PL22 0QJ
Tel: 01208 872 963 / 0845 458 4719
Email: webmaster@pfaf.org
PFAF electronic mailing list http://groups.yahoo.com/group/pfaf







Archive powered by MHonArc 2.6.24.

Top of Page