Permaculture Information Web

[Chad Knepp <pyg AT galatea.org>]

Responding to Marco and Sean... two for the price of one!

Sean Maley writes:
 > This may be more appropriate for the pcplantdb
 > listserv.  Perhaps it should be carried over.

Lawrence, how about adding Marco <marco.stahl AT gmx.net> to pcplantdb?
Ok with other folks?

 > Your thoughts are just as valid as mine below. 
 > Hopefully I am adding to the discussion. 
 > Unfortunately, I don't have enough time to think this
 > through, so feel free to sling mud if I'm missing
 > anything.
 > 
 > --- Marco Stahl <marco.stahl AT gmx.net> wrote:
 > 
 > > Hi all!
 > > 
 > > Maybe its possible to express Relationships as
 > > INPUTS/OUTPUTS of an entity
 > > (plant, animal, human, machine,...).
 > > 
 > > An Entity could also be a category.
 > > 
 > > Example:
 > > 
 > > entity | parent
 > > ---------------
 > > creature NULL
 > > animal   creature
 > > plant    creature
 > > bean     plant
 > > hemp     plant

I think this is neat technically but I'm not sure why we need this
information except for finding less specfic information when the more
specific is missing.  For example, not knowing something specific
about cabbage is not a problem if I know something general about
brassicas.

 > Where searches represent the fundamental activity with
 > the dataset, OLAP, we may consider keeping the schema
 > denormalized; creature_type: A, M, V (animal, mineral,
 > vegetable) rather than get into hierarchical data
 > sets.
 > 
 > --------------------
 > |creature_dimension|
 > --------------------
 > |creature_key      | : hemp, bean, etc
 > |creature_type_key | : A, M, V
 > |ground_depth      |
 > |height            |
 > |....              |
 > --------------------

Bear has also suggested a denormalized schema and on a theoretical
technical level I tend to agree that this is a good direction to go.
Unfortunately it we loose most of the advantages of using an RDBMS
when doing so.  Our initial direction toward this was using an object
DB, but was thwarted IMO, by the fact that ZOPE2 just plain blows,
although I think ZODB is awesome.

Also your example is not fully denormalized in that every schema
element should apply to every item.  Do all animal, mineral, and
vegetables have a ground_depth and/or height that makes sense.

IIRC, Bear suggested a two/three column schema with a primary key and
then an attribute and followe by data of the attribute.  Something
like:

id | attribute  | data
---------------------
34   height/feet  20

As I said earlier this doesn't scale well in an RDBMS.

Coming full circle and replying in part to Marco the implementation
I've been pushing for relationships is a loose tagging (uncontrolled
vocabulary) one a'la <http://del.icio.us/>.  Read
<http://www.adammathes.com/academic/computer-mediated-communication/folksonomies.html>
for more information.  This is a great paper BTW.

 > > entity  | OUTPUT
 > > ---------------
 > > bean   nitrogen
 > > hemp      climb help
 > > hemp      seed
 > > hemp      fibre
 > > 
 > > 
 > > entity  | INPUTS
 > > ---------------
 > > bean   climb help
 > > hemp      nitrogen
 >
 > Most outputs have a corresponding output.  So it isn't
 > enough to say your input/output is some quantity, but
 > rather some input yields some output.  Additionally,
 > most relationships are complex, so there are numerous
 > inputs that yield numerous outputs.

I'm rather in agreement with Sean here and not sure that this is the
best (or even an adequate) division of all the possible relationships
entities have with each other.  When I try these categories I find
Inputs to be too abstract... as in I get bogged down when trying to
list all of the things a plant could need.  Listing Outputs is much
more obvious to my mind.

 > -------------------
 > |product_dimension|
 > -------------------
 > |product_key      | 
 > |product_desc     | : nitrogen, seed, fiber,
 > |                 | : climb help, etc
 > |....             |
 > -------------------
 > 
 > -------------------
 > |relationship_fact|
 > -------------------
 > |creature_key     |  : bean, hemp, etc
 > |in_product_key   |  : N, seed, fiber, etc
 > |min_in_amount    |  : +/- qty, g, l, m, etc
 > |max_in_amount    |  : +/- qty, g, l, m, etc
 > |out_product_key  |  : N, seed, fiber, etc
 > |min_out_amount   |  : +/- qty, g, l, m, etc
 > |max_out_amount   |  : +/- qty, g, l, m, etc
 > |production_time  |  : days
 > |....             |
 > -------------------
 > 
 > The sign accounts for when an input causes a decay for
 > a given output; over watering, nutrient burning, etc.

OTOH, this seems overly complex to me.

 > > 
 > > 
 > > We could also include the amount of the produced or
 > > needed product and
 > > reduce OUTPUT and INPUT to INPUT/OUTPUT. 
 > > 
 > > entity    | (INPUT)/OUTPUT    | amount
 > > -------------------------------------
 > > bean   nitrogen     100
 > > bean      climbhelp         -100  
 > > hemp   nitrogen          -100
 > > hemp      climb help         50
 > > hemp      seed       50
 > > hemp      fibre       50
 > > 
 > > 
 > > If Hemp is normaly a average "climb help" but a good
 > > one for beans, we could
 > > express that in this way:
 > > 
 > > entity    | (INPUT)/OUTPUT    | amount
 > > -------------------------------------
 > > bean   nitrogen     100
 > > bean      climbhelp         -100  
 > > hemp   nitrogen          -100
 > > hemp      climb help         50
 > > hemp      climb help bean    100
 > > hemp      seed       50
 > > hemp      fibre       50
 > > 
 > > or alternativ:
 > > 
 > > entity    | (INPUT)/OUTPUT    | amount |
 > > targetentity
 > > --------------------------------------------------
 > > bean   nitrogen     100         
 > > bean      climbhelp         -100  
 > > hemp   nitrogen          -100
 > > hemp      climb help         50
 > > hemp      climb help         100        bean
 > > hemp      seed       50
 > > hemp      fibre       50
 > > 
 > > 
 > > Advantage of this approach:
 > > ---------------------------
 > > 
 > > * No need for writing 10000000 relationshipentries
 > > just for the fact, that 100 plants produce something
 > > that all other plants need (can even
 > > reduced/automated more with help of categories)

This is pretty interesting to me, but I'm not sure if I understand how
amount works as well as target_entity... what is your key?  The
target_entity for (hemp, climb help, 100, *) could also be peas,
luffa, etc.  How do you get away from listing them all?

Even with a well devloped entity|parent table, I'm not so sure that
you will automatically be able to generalize with sufficient
accuracy.  So many things have exceptions to the rule.  Some of the
seeds of the genus Prunus are edible (Almond) and some of them are
rather poisonous.

 >   10 00 00 00
 > 
 > This is what hierarchies do to IO and why 10,000,000
 > records can be more effective.  First, an index can
 > get you into that 10,000,000 with reasonable
 > efficiency.  However, an index may not be effective on
 > 100 records, so all your searches become 100 with an
 > exponent of your hierarchy depth with full table
 > scans, rather than an index scan.  I'm not saying it
 > isn't the answer, but one should be very wary in terms
 > of using it as an architecture; real record numbers
 > need to be specified prior to making this decision.
 > 
 > If you ended up with 1000 records instead of the
 > anticipated 100, you get:
 > 
 >   10 000 000
 > 
 > Three hierarchies deep become significant relative to
 > the work being demanded verses having room for four
 > levels of hierarchy.  Although this over simplifies
 > the discussion, a healthy respect for hierarchical
 > queries is required nevertheless.
 > 
 > > * you can easely integrate other entities than
 > plants
 > > * you can easely express uses as products (and
 > inputs
 > > of the entity human)
 > > * you could even express stuff like "a plant needs
 > > wet soil, high temperature"
 > > * maybe: (semi)automatic
 > > guild/permaculture-design-generation
 > > 
 > > 
 > > I hope you like my ideas,
 > > 
 > > Saludos, Marco

As an alternative proposal here is my conception of the loose tagging
model of relationships.  

Simplified schema:

Entity | Relationship (loose tag) | Optional Entit(y|ies)
---------------------------------------------------------
Bean       nitrogen fixer           set(all plants)  *perhaps soil?!?
Peas       needs cool weather       NULL
Rhizopus   tempeh culture           Soybean

This still needs work because I'm not sure how one would create a
guild with it or extrapolate other guild possibilities.  I don't
really like the Optional Entities column but I can't think of how to
relate diffent entities in a way that actually tracks related
entities.  Simply including *Soybean* in the tag doesn't make it easy
to link it to Soybean the plant.  Also both entity columns need to be
set capable with on a simplistic level at least awareness of set(all
plants), set(all plants of family X), ...family X and genus Y, and so
on.  Implementation would not actually use sets in the column of
course.

Cheers,
Chad

-- 
Chad Knepp
python -c 'import base64;print base64.decodestring("cHlnQGdhbGF0ZWEub3Jn")'