Skip to Content.
Sympa Menu

pcplantdb - Re: [pcplantdb] schema stuff

pcplantdb@lists.ibiblio.org

Subject: pcplantdb

List archive

Chronological Thread  
  • From: Richard Morris <webmaster@pfaf.org>
  • To: Permaculture Plant Database <pcplantdb@lists.ibiblio.org>, Ken Fern <ken.fern@lineone.net>
  • Cc:
  • Subject: Re: [pcplantdb] schema stuff
  • Date: Wed, 17 Nov 2004 00:36:11 +0000

Chad Knepp wrote:

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.

Cool, a message I can reply to!

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.

Sounds good. Pos consider Java style nameing ie PlantName
but this might cause problems on windows boxes which are
wierd with capitilisation systems. For this reason I'd say
keep table names all lower case (in mySQL table names follow
capitilisation rules of underlying OS). Not sure about field names.

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.

Yes, I've been increasingly thinking about going a pure XML route
for the plant data. i.e. rather than a table with lots of fields
have a single field with XML data inside. This could make for a more flexiable solution as for instance you might have a cultivar
where you might want to record its height, developing a database schema
to cope with this and all other conceivable posibilities would be tricky. An XML route makes this easier, there are considerations about the DTD to bear in mind though.

I am very keen to have as much marked up as posible, I've attached an image of a page from where I've got to with the pfaf db. In it I've been trying to make things as graphical as posible with icons for edible and medicinal ratings, type of plant (Tree, Shrub, climber etc), height, width. The last three are included in resisable graphic which gives a proportional tree height (code and icons available on request).
To do this the data needs to be easily extractable, either
as fields or as marked up xml items.

phpBB a bulitin board system
as a nice easy way for users to markup text. Basically works the way underlining and bold works in MS-Word, i.e. hightlight a value and press a button, in Word its 'B' for bold. In our app we could have a 'height' button.

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 |

smallint(6)! smallint(5) could be a bit tight, even numbers are good.

| 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 |

variety | varchar(50)

cultivar | varchar(50)

| 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.

Indeed its not. Generally a sub-species, but there are exceptions.

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.

Yes, to be botanically correct the author is part of Latin name.

common_name should go in it's own [many to many] table with
locale support.

Yes, I think it should be in its own table. Yes locale support
is a good feature (could leave until later).


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.

The thought has crossed my mind that the botanical name and synonym
are really the same type of thing. Could potentially have one table for both, what do you think?

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.

These hybrids eg Elaeagnus x ebbingei are a real pain. UDSA includes
fields for represting these types of thing, a good way to go I think.

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.

Ken's been working on this table a bit and has reciently cleaned things up to remove duplicate references etc.

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.

Good.

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?

XML!

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.

I really think Book references should stay. They provide a means where
data can be checked.

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.

There are lots of cases where different key words are used, this being
one of them. Its posible that we could generalise things to have a general Keyword table, with links to related pages describing the KeyWord.

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.

Essential for some of the medicinal uses, where the technical term used really need a fuller explination. See
http://www.comp.leeds.ac.uk/pfaf/D_med.html
For instance
Antihalitosis | Treats bad breath
some sort of link to a page describing the key word
might be appropriate here.


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.

What would be really good is if this table could be expanded, i.e.
set some people to the task of trying to find composition for plants not mentioned. XML could be a way to include this info
without having to create a whole new table. Qite a few other things like yields, which woul be great to add at a leter date.

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.
Yes, I agree. I've code which does free text searches on the pfaf range data to extract names of countries mentioned.

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.

Latest pfaf db, now has edible and medicinal ratings. Lots of scope
for users to rate plants and other expansions.

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.

Yep, definatly stage 2.

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


The one very major item is how we handle edits. How do we keep a history of past edits to protect against vandalism, etc.

Good stuff

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

PNG image




Archive powered by MHonArc 2.6.24.

Top of Page