Permaculture Database

[Paul d'Aoust <paul@heliosville.com>]

So, in continuation of last night's e-mail...

On Sat, 2009-03-21 at 01:12 +0000, jedd wrote:
>  I should say that up front, my design goals might not match
>  yours, or indeed anyone else's.

Yes, I was quite astonished by your intended scope -- I was just
thinking of a nice database of plants and animals and their
characteristics/needs/tolerances, with a bit of information about
positive and negative interactions among them. But I do like the idea of
location- and time-based information -- journals, if you will -- that
extrapolate and compile information as various people update their plant
lists and successes/failures/local temperatures each year. Sounds pretty
ambitious!

>  I considered trying to write a plug-in to my wiki/CMS (I am using
>  deki, and it has much potential for mash-ups, plug-ins, etc) but
>  quickly concluded that, ultimately, realistically, the scope of my
>  plug-in would soon exceed the scope of a CMS or wiki by several
>  orders of magnitude - it'd be easier to write my own CMS component,
>  or drop one in, later on, rather than approach it from the other way.

Yeah, I was thinking early on that this project needed a little more
fine control than a wiki would afford us... but there are some great
ideas that we can take from a wiki -- for instance, I think we should
still track revisions in the writeup of a node using deltas.

>  (Well, actually, I did start to ponder a way, using a fixed set of
>  'affects' attributes - maybe a scale of 10 or 20 - with the middle
>  being zero affect, negative being adverse, etc.  The relationship
>  would thus have a quanta attached to it, and then just requires a
>  descriptive aspect - such as 'shades'.  So something can shade
>  something else, and this has an affect (including zero affect).
>  This would allow a fair level of abstraction, and involves lots of
>  tall thin tables (which is encouraging from a design POV) but
>  the determination of affects, and the location/climate/soil/etc
>  influences of how much that affect is, is where I get bogged down.)

Yes, I was thinking along those lines as well. I think that -10 to +10
would be perfectly adequate for describing interactions. I'll get into
this later if I ever manage to find those ER diagrams I scribbled out,
but I thought it might be useful to model relationships in two different
ways:

1. The 'affects' way, which might look like this:

affecterID int foreign key
affecteeID int foreign key
influence smallint
label varchar

So the influence field would describe the strength or significance of
the interaction, and the label would be a place to put 'shades', as in
your example.

2. A 'source-and-sink' method, which would list all a node's possible
needs, functions, and yields... We'd have two tables, like this:

sourceSink
----------
nodeID int foreign key
value smallint
functionID int foreign key

function
--------
functionID int primary key
label varchar

So the value would again be -10 to +10, in which a negative number
denotes a need (phosphorus, sunlight, well-drained soil), and a positive
number describes a function or a yield, which could be seen as pretty
darn similar things, from a data design point of view (I've always
thought).

So a query on the node, sourceSink, and function tables might yield a
result like this:

SELECT * FROM node, sourceSink, function WHERE node.nodeID =
sourceSink.nodeID and sourceSink.functionID = function.functionID;

nodeID  node.label  value  functionID  function.label
3       tomato      -8     1           sun
3       tomato      +8     2           human food
3       tomato      +3     3           shade
3       tomato      -4     4           water
3       tomato      -4     5           warmth

But now I can see what makes this design confusing: What is 'water'? Is
it an input, or can it be a node in its own respect, or both? What sort
of sun does it need? Obviously we know it needs a lot, being that it's
-8. But what character of sun? full sun? can it survive in lots of sun
even if it's cold? Obviously not. So then we know that it needs a fair
bit of warmth, but then what sort of warmth and cold can it handle
specifically? These are the questions I'm still struggling with.

>  Heck, I can't even work out a way of managing the basic
>  taxonomy - I've looked at tree hierarchies, but I think bio
>  taxonomy breaks a lot of the requirements of most MPT(?)
>  style libraries that plug in to an RDBMS.  I'm thinking of the
>  gaps, the optional taxa, the handling of deprecated synonyms
>  and so on.

I think that an adjacency list might work well... I know you can
accidentally create loops, in which node 1 is a parent of node 5, which
is a parent of node 7, which is a parent of node 1, but I think that
sort of constraint could be imposed either in PHP code or by forcing
certain operations to run through stored procedures. I can't think of
any other way to create multiple inheritance.

I dunno... I had been thinking of genera as one level of the tree, so
then you'd have

 Solanaceae
    Solanum
       lycopersicum *
       tuberosum
       esculentum
    Lycopersicon
       esculentum *

etc. The asterisked node would belong to both genera, of course. This
would allow us to indicate common needs, which would be useful in genera
whose species are quite similar. This might get a bit tangly though. So
we could just do a simpler tree, in which each plant or animal gets its
own listing regardless of genus:

  Solanaceae
     Solanum lycopersicum / Lycopersicon esculentum
     Solanum tuberosum
     Solanum esculentum
     Datura stramonium

and so forth. Because labels (scientific or otherwise) would be
abstracted out into another table, we could attach as many tags as we
wanted without getting convoluted trees. Every so often we'll get
something like a family split, but that would be a fairly simple tree
operation compared to the always-undulating species names :-)

>  I'm currently pondering geographical information - looking at how
>  I can define a location, what scale to use, how I can do sub-sites
>  within a larger site, if that should be recursive or just have a limit
>  of N levels, say.  Do I use sunset zones, USDA zones, require the
>  user to enter some climate data to identify my own metric for
>  comparing climates/zones, or allow all of the above.

The WWF Ecoregions classification seems kinda neat:
http://en.wikipedia.org/wiki/List_of_ecoregions_(WWF)
I think that we should dispense with USDA zones, because they're not
used all over the world (and we can create microclimates too), and just
describe a plant's cold/heat tolerance instead.

>  Hmm .. after writing all that .. I'm wondering if it might be worth
>  setting up a mini-wiki for us to, in the very short term, identify
>  our respective intents - pseudo-page mockups say - and get an
>  idea on whether we are, or at least could be, heading in roughly
>  the same direction.  Note that I'm being *very* narrow with the
>  intent of this repository - it'd be purely to discuss ideas on design
>  approaches, schemas, etc.  There's be no mention of peach trees
>  anywhere in the thing.  :)

Aw, even if we're using a peach tree as an example? How about chickens?
We can talk about them, can't we?

Anyway, yes, a wiki might be a good idea. Is there some sort of free
place that caters to things like this? Perhaps some space on ibiblio?

More later -- I've gotta start work!

Paul d'Aoust