pcplantdb

[Chad Knepp <pyg@galatea.org>]

Ok here is some schema stuff.  You'll need to have a fixed width font
and at least 100 character wide display to see this correctly.  If the
tables don't line up you probably have a proportional font.

Some norms I'd like to introduce:

  o Table naming:  For increased clarity I'd like to start every table
    with the first one or two words being the type of objects
    contained/related.  Eample: plant_name, relationship_types, and
    for indexs plant_relationship_index, etc.

  o XMLized dataset:  The one place where I think XML actually does
    make a lot sense is inside the database itself.  Three tags I can
    think of immediate are temperature, height, and link tags.
    Clients in the US will want Fahrenheit and Feet while Europeans
    will want Celsius and Meters.  Non html based clients will need to
    be able to translate links into something meaningful.  

  o Although I would like to have a master index of integer ids
    referencing everything in the dataset I haven't figured out a
    fully normalized implementation.  Possibly forthcoming or
    otherwise a naming scheme will be implemented with elements like
    plant1537 and comment683 ([a-z])([0-9]). 

Some comments on the existing schema and proposed changes:

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     |
+------------------------+----------------------+------+-----+---------+-------+------------+
This should change to plant_name or plant_botanical_name.  One of the
significant ways the eden dataset difference from the pfaf one is that
the primary key for most tables in pfaf is a 2 - 3 word Genus species
ssp/var/forma combo.  Eden uses an integer id and separates the latin
name into it's component parts.  It's not clear to me what exactly the
third word of the name represents (either ssp, variety, forma,
whatever) and I don't think the pfaf dataset is clear about it either.
Not sure where to go with this.  legacy_pfaf_latin_name is retained in
order to syncronize with later pfaf datasets.  I didn't think it was
important at the time but [latin name] author should probably go back
in here.  common_name should go in it's own [many to many] table with
locale support.

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     |
+------------------------+----------------------+------+-----+---------+-------+------------+
This is a many to one table that should be renamed plant_name_synonyms
or some such.  Interestingly enough author remains...  Probably can
drop legacy_pfaf_latin_name in this table.

Database: eden  Table: hybrids  Rows: 98  Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type                 | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id    | smallint(5) unsigned |      | PRI | 0       |       | select     |
+-------+----------------------+------+-----+---------+-------+------------+
Kind of related to plant names is hybrids.  Some of the plants are
hybrids and it is not indicated elsewhere.  Eden currently does not
check/display this.  This should maybe go back into the plant_names
and plant_names_synonyms tables, or into the plant_culture below.

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     |
| 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     |
+---------------------+----------------------+------+-----+---------+-------+------------+
This is the table that serves as the basis for the paragraph long
summaries produced for each plant.  Either this needs to change from a
one to one table to a many to one or the localized info needs to be
split out to a new table.  As I suggested I would like to change
height_centimeters and width_centimeters to height and width
containing something like <distance type=centimeters>1200</distance>.
As we discussed earlier there are many additions and changes that
apply to this table that I won't reiterate here.

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     |
+-----------------+----------------------+------+-----+---------+-------+------------+
This is another table that comprises part of the summary data, but
needs a bit of refocusing I think.  I can think of way more possible
categories but listing them all and evaluating each plant for
qualification seems like overkill.  Perhaps this kind of information
can be captured by relationship information.

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: 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    
 |
+-----------+----------------------+------+-----+---------+-------+------------+
These two tables are paragraph sized comments about the plants.  I
suggest that they be incorporated into a comments table.  Since we
need to have comments be able to be attached to anything including
plants, relationships, and other comments this needs to be carefully
thought out.  The content of these comments also really need
XMLization as they are primarily written by Ken Fern and are full of
reference like [49, 67] where the numbers refer to ids in the
references table but have no usable linkage.  These should be changed
to <link> tags.  At one point I wrote a script that parsed every
comment of every plant for every reference of this sort and generated
what is now called references_index.  After the XMLization we could
drop this table.

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     |
+-------------------+----------------------+------+-----+---------+-------+------------+
Although there aren't many listed yet (1902) I think users will
probably submit a lot of cultivar info.  I'm not sure if this should
be handled as comments (the cleaner method) or continue with a
plant_cultivars table.  Waddya think?

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     |
+------------------+-------------+------+-----+---------+-------+------------+
The 270 books that Ken Fern pawed through to create the dataset.  In
the past I have been in favor of dropping this table because although
the dataset was created largely by referencing books I seriously doubt
it will progress that way.  Most of our data from now on I expect to
get from users sharing personal experience who will not reference
books.  See also references_index and uses_text/cultural_notes.

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     |
+-------+----------------------+------+-----+---------+-------+------------+
This is a many to many table with plant ids corresponding to what they
are used for.  I would like to have a better way to do this.

Database: eden  Table: uses_glossary  Rows: 271  Wildcard: %
+------------+--------------+------+-----+---------+-------+------------+
| Field      | Type         | Null | Key | Default | Extra | Privileges |
+------------+--------------+------+-----+---------+-------+------------+
| uses       | varchar(100) |      | MUL |         |       | select     |
| definition | tinytext     |      |     |         |       | select     |
+------------+--------------+------+-----+---------+-------+------------+
Rather pedantic definitions of the various uses.  I would like to drop
this table.

Database: eden  Table: cache  Rows: 102  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 |
+---------+---------------+------+-----+---------+-------+----------------------+
This table is complete internal and a performance enhancing aspect of
eden as opposed to being part of the dataset proper.  Just ignore this.

Here are some legacy pfaf tables that are currently unused by eden
that I recommend dropping until/unless we need them.  Don't worry
about information loss, because we can always import it again from an
archive of the eden or pfaf dataset at a later date if desired.  I'm
not sure that even pfaf uses these tables.

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     |
+---------------------+----------------------+------+-----+---------+-------+------------+
This table has nutritional information for 146 plants... too small to
be usefull IMHO.

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 an attempt at some localization in the form of where the plant
lis native.  I say we drop this and do a more focused version of
localization. 

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     |
+-------+----------------------+------+-----+---------+-------+------------+
A generated index that could be dropped if the dataset was properly
XMLized. 

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     |
+-------+----------------------+------+-----+---------+-------+------------+
I really liked this concept early on and pcpdb (pre-eden search
engine) sorted search results by order of rating.  Not sure how this
figures into the whole scheme.  Perhaps this is a table to be dropped
as we will probably have a more advanced scheme for qualifying
information.

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  
   |
+-------------+----------------------+------+-----+---------+-------+------------+
1169 plants and how they smell.  If this is important stuff we should
add it to plant_culture, otherwise drop it.

Other major additions include:

  o Users and authentication:  I think this will be fairly easy and
    I'll just store all user/editor info with hashed passwords in a
    table.  

  o Relationships: Still thinking on this some, and somewhat
    intimidated by the kind of table magic I suspect will need to
    happen but I can at least see a table defining different sorts of
    relationships and table(s) that relate different elements with
    each other... possibly with some kind of set notation... possibly
    in XML.  I'll get back to you on this one.

  o Comments: As before I really want to see comments (and
    relationships) being able to be attached to almost anything in the
    dataset.  Comments themselves are fairly straight forward in terms
    of dataset manipulation.

  o Images: This should be very straightforward.

  o Other kinds of elements:  Farther off on the horizon is the
    possibility of adding animals, techniques, articles, etc.  I'm not
    going to focus on any of these things at the moment, be I am
    keeping in my head their possibility and developing so as not to
    exclude them.

Enough for now.  I will send another email today about a proposed
development path.

-- 
Chad Knepp
Perl - $Just @when->$you ${thought} s/yn/tax/ &couldn\'t %get $worse;