pcplantdb

[Chad Knepp <pyg@galatea.org>]

Lots of good conversation, so I'm going to collate and summarize and
<snip> with out specifying

Richard Morris writes:
 > > | id                     | smallint(5) unsigned |      | PRI | 0       | 
 > >       | select     |
 > 
 > smallint(6)! smallint(5) could be a bit tight, even numbers are good.

Not unless we have more than 1.078203e+12 plants!

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

My question remains.  How do we distinguish between ssp and variety?
No such distinction is made in PFAF that I can tell.

 > cultivar | varchar(50)

This has it's own table.

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

I'll put author back in.

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

I still don't want to design something that makes the later addition
of locales very difficult.

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

Well you don't have an integer primary key then because it would be a
many to one type of table.  You could have a multipart primary key of
family, genus, species, ssp/var, and author though.  Not sure about
this one.
 > > Database: eden  Table: plant_locations  Rows: 7384  Wildcard: %
 > > +-----------------+----------------------+------+-----+---------+-------+------------+
 > > | Field           | Type                 | Null | Key | Default | Extra 
 > > | Privileges |
 > > +-----------------+----------------------+------+-----+---------+-------+------------+
 > > +-----------------+----------------------+------+-----+---------+-------+------------+
 > > 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.

Cool, I expect that will be in the new pfaf dataset.

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

I don't understand.  That's not really answering my question.

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

I'll leave it, but I think in the future more like citations will come
from web references so it will need some adaptation.

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

Thats what this and the next table actually do.  It's fully functional
in Eden right now.

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

Yes, the medical ones aren't intuitive but many of the uses (like
food) are rather obvious.

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

This is an important and BIG discussion that we've touched on in the
past.  At the moment I've been considering only self editing of posts
and no delete, but allowing posts to be rated so that higher rated
posts become more visible and low rated ones don't show unless
specified (a'la slashdot).  Any deleting (of extremely low rated
posts) would be probably be handled by a daemon.  I expect y'all will
have lots of interesting stuff to say about this as well.  We don't
have to talk about it just yet, because it's a little ways off and
basic comment functionality can be implemented without all of this.

Bear K writes:
 > Hello all,
 > 
 > > 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.
 > 
 > I was actually going to suggest something along those lines. A book I 
 > have ('Edible Wild Plants') has a set of icons representing the 
 > different uses of the plant (flour, candy, milk, cooked greens, ...), I 
 > thought it would be cool to have a largely visual output for the plant 
 > information, including things like water use, light preference, etc. 
 > Given say 16 or 20 pixels each icon (aside from multiples like ranks, 
 > water, and light) you could present a lot of information in a small 
 > space. This could come in very handy if say there was a functionality 
 > to select a list of plants and then get a printable copy with info on 
 > the various plants. Of course this would just be one potential view 
 > option.

I do like the idea of representing physical characteristics visually.
Something that Richard Morris suggested as well.  This is not really
schema dependent though, because the engine/client is the agent to
transform the data into other representations.

 > Regarding XML, which I also think could come in quite handy, one issue 
 > would seem to be the speed of searching single fields filled with XML 
 > for specific properties. Seems like a slow process. Is it there fast 
 > method I'm not aware of?

You are right on, but I'm going to respond to Richard Archer on this.

 > I think it would also be great to have a relationship field for 
 > beneficial or antagonistic  relationships between plants. In the DM (pg 
 > 62, 63) collecting this data is mentioned. Seems like a good place to 
 > gather it. Or was this already discussed?

Yes, relationships between permaculture elements (plants at this
stage) is one of the primary focal points of the project.  I didn't
put a lot about in the schema details because I don't fully grok how
to do it (will probably do it wrong at least once)...  More good ideas
re: an RDBMS way of doing this cleanly appreciated.

 > Lastly, if ibiblio is slow to respond, I'm paying for hosting of 
 > OpenPermaculture with a rather good plan, so I can host there for a 
 > while if need be. I should be able to get chad an SSH account if 
 > needed. MySQL space is there, and available bandwidth.

Thanks for the offer, but I don't really want to spend the time
setting things up for a short amout of time.  I am still hoping that I
can move Eden onto Ibiblio before I leave for Turkey day, but time is
drawing nigh...

Richard Archer writes:
 > At 12:09 PM -0600 16/11/04, Chad Knepp wrote:
 > 
 > >  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.
 > 
 > XML is not the correct solution to that problem. What you need to do
 > is store the values all in either Metric or Imperial and convert them
 > at display time based on the user's preference. This allows efficient
 > storage and search of numerical values by the SQL database. It also
 > allows simple joins which would quickly become a nightmare if the
 > data in the database is all encoded in XML.

Right on.  The example I gave was plain wrong.  I didn't even consider
at the moment that having a mixed column of celsius and fahrenheit
tempatures would completely nix searchability.  

 > IMHO the ONLY valid use for XML is for data interchange. e.g. if you
 > wanted to regularly move data from the Eden database to say the PFAF
 > database you might want to use XML as an interchange format and have
 > an XML generating/parsing script at each end to convert from the
 > common XML format to the local format.

Yes, and the particular need that I'm refering to is data
interchange.  Here is an example:

   Edible Uses: Fruit - raw or cooked[1, 2, 3, 37]. It can be used as
   a savoury vegetable or flavouring in cooked foods, or can be eaten
   out of hand as a dessert fruit. It is much used in salads and as a
   flavouring in soups and other cooked foods[183]. A juice made from
   the fruit is often sold in health food shops[183]. The fruit can
   also be dried and ground into a powder that can be used as a
   flavouring and thickening agent in soups, breads, pancakes
   etc[183]. An edible oil is obtained from the seed[46, 61,
   171]. Suitable for culinary purposes[183]. The seed is small and it
   would be very fiddly to utilize. It is only viable to use the seed
   as a source of oil if large quantities of the plants are being
   grown for their fruits and the seed is not wanted.

>From the edible use section of tomato you can see a paragraph of text
with refrences (bracketed numbers).  These numbers refer to books in
the references table.  The value of converting these bracketed numbers
to things like <link location=reference+1>1</link> is that this
information can be rendered in a client specific way.  Html for a
browser, bracketed numbers for a text client, or a preference option
to not see them at all.  I also think that imperial and metric
specifications in a text blurb would be nice if they were in XML so
that they could be rendered to the client preference.  This would not
decrease searchability of a chunk of text in a significant way.

John Schinnerer writes:
 >   > 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     |
 > <snip>
 > 
 > I think 'culture' is wrong name for this sort of stuff.
 > I would like 'culture' to be keyed to ethnobotanical information.
 > Not sure what to call this grouping...there's quite a mix of info in 
 > there...functional stuff, performance stuff, inputs/outputs stuff...

Yeah, it could be plant_info or some such, but the actual table name
(other than conforming to a scheme) is not that important to folks
other than developers because it is quite internal to the end user.
No one will know that a certain amount of information comes from a
table called plant_culture.

 > > 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.
 > 
 >   > 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.
 > 
 > This looks like functional and relational information - very PC stuff, no?

Yes, but I can think of thousands of habitats that a plant may or may
not do well in.  My complaint is I don't think just listing all the
habitats is the way to go.

 > >  Perhaps this kind of information
 > > can be captured by relationship information.
 > 
 > Don't follow - how would the relationship information that would capture 
 > this be stored?

Well, instead of listing all the possible habitats with a no/probably
not for most of them, a plant could just have a [expandable] set of
relationships that include known favorable and unfavorable habitats.

 > > 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     |
 > > +---------------------+----------------------+------+-----+---------+-------+------------+
 > 
 > Should also be known_uses...and...??

That exists, but in a different table uses_index at the moment.  It is
also a many to many type of table instead of a string of
uses... something like this:

+----+-------------+
| id | uses        |
+----+-------------+
|  1 | Wood        |
|  2 | Fibre       |
|  2 | Paper       |
|  2 | Size        |
|  4 | Essential   |
|  4 | Fibre       |
|  4 | Insecticide |
|  4 | Oil         |
|  4 | Size        |
|  5 | Essential   |
+----+-------------+

 > Actually if this is to be ethnobotanical info a lot more thought is needed.
 > For some reason just a few text fields seems insufficient - depending on 
 > how they would be editable.
 > cultivation_details and propagation_details may have multiple stories 
 > each, depending on the culture doing the cultivating and/or propagating.
 > Maybe other factors too such as regional conditions that change the 
 > necessary cultivation and/or propagation info.
 > Throwing it all into one text field, hmm, I don't really know but it 
 > seems like that would be limiting and/or messy and/or hard to search 
 > effectively...?

Yeah, I've been struggling with how to structure shifting this kind of
information to pure comments because currently it all exists in very
structured categories of cultural notes, propogation notes, know
hazards, edible uses, medicinal uses, and misc uses.  Should comments
fall under non-arbitrary topics?  How do you limit the the number of
subject/topics if not?

It's not actually hard to search effectively... one of the beauties of
an RDBMS.  You can do a full text search on any chunk of text very
quickly.  The current dataset only has about 5-7 major chunks of text
to search per plant.  

 > > 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?
 > 
 > How would comments be cleaner?  You mean for implementation, for user, 
 > both?
 > How would they be stored and searched?

Well, I keep coming back to the problem of having a new table for
every kind of comment.  I'm sure we can't think of all the kinds of
comments/text blobs that we are going to eventually want, so I keep
looking toward creating the generic concept of a comment.

Regarding the whole to XML cultivar or not by Richard M. and John S.
My take is that I don't think XML is as important here because there
is not a need for data interchange.  Things that we need to translate
to our users should be in XML and nothing else.  We do have a seperate
table for cultivars and I would like to figure out how to make them
comments, but still not sure about all that.

Anyway, I bet I've generated some new fuel for the fire.

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