Permaculture Database

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

Hello, Jedd, Paul C, et al...

On Sat, 2009-03-21 at 01:12 +0000, jedd wrote:
> Hi Paul,
> 
>  Ahh .. I'm partly envious, as I have a bit of forced time on my
>  hands as I attempt to find my next contract.  Hence the looking-
>  around for some coding project to keep me busy.  :)

Gosh... I'm up to my ears in work at my 'day job' and having clients
throw programming work at me that I have to turn down because I don't
have any time to be freelance. I guess we always envy what we don't have
-- I'd love to be in your shoes right now.

>  I'm using CodeIgniter, a PHP framework, and I'm mostly just
> stretching the mental muscle as I do some learning.

I'd heard of CodeIgniter, but I didn't know what it is... Gee, that's an
issue I never even thought of -- which of everyone's favourite app
frameworks do we use? CodeIgniter looks exciting, but I'm a little
worried about the fact that it's meant to run on PHP4, when PHP5 is so
widespread now. (Unless I just read a really old user manual.)

>  PFAF - I think they're pretty free with who they share their data
>  with - but I'm not sure how easy it would be to import it into
>  a more formalised database.

Oh, I dunno, I think that with a little creative field-twisting we could
squeeze anything into anything. I love doing that sort of stuff
myself :-) The reason I would like to start with an established bse of
knowledge like PFAF is that even a beta site would be immediately useful
for permies and gardeners everywhere, and would thus generate more
traffic, edits, bug reports, comments, questions, etc.

> Then there's the encyclopedia <sic> of life mob:
>    http://www.eol.org/  

ha, I guess you're from across one of the ponds from me :) Although I
affect that conceit just to confuse my uncultured friends.

>  I only want to track useful organisms - so that rules out 95% of
>  the species on the planet - but I also want to separately track
>  each of the sub-species, varieties, forms, etc.

I was thinking about that same thing, because, say, my early-harvest
'Stupice' variety tomato is a lot more cold-tolerant than, say, the wild
Chiapas tomato. Collecting data on cultivars would be immensely useful.

This, for me, is where questions of hierarchy come in. It would be neat
to do some sort of cascading -- for instance, both Stupice and wild
Chiapas would share similar needs as any other tomato -- pH tolerance,
light exposure, etc -- but would have variations in other factors, like
cold tolerance, yield, or onset of first fruits. Looking at the species
node ('Solanum lycopersicum', if that's what you like, or perhaps
'Lycopersicon esculentum') you would see a combined summary of all data
collected on all cultivars ('this plant is perennial down to -??° to
-??°') and cultivars would inversely gather data from their parent in
places where their own data are incomplete.

Already there's an issue of hierarchy. We could standardise on a
nomenclature system -- say, Linnaeus, arbitrarily, who called it
'Solanum lycopersicum' -- but then what if someone's looking for
'Lycopersicon esculentum'? Well, we do know that it still fits in the
Solanaceae, but we know already that we're going to have problems, as
you were saying.

I think a solution would be to use an adjacency list (one junction table
with 'parentID' and 'childID') rather than an MPTT, because we have
different genera for the tomato and MPTTs can't have multiple
inheritance. I know an adjacency list is less efficient and sometimes
more prone to broken branches, but it does afford us multiple
inheritance, and it's human-readable.

Then, the name. Perhaps each node could be a very plain, stripped-down
object, with most of its other data in another table? For instance, we
could have a 'label' table, with the following structure:

label varchar(255)
nodeID int(11)
scientific bool

The label couldn't be a primary key, because you have lots of things
sharing each other's name -- like 'syringa' for both lilac and
philadelphus, a bush native to my province -- but the combination of
label and nodeID could be a unique index. nodeID would of course be a
foreign key to the nodes table, and scientific would mark whether this
label should show up in the list of colloquial names or scientific
names.

This is way too interesting, and I haven't even started to scratch the
surface of what you guys have been talking about -- but it's way too
late, and I have to take my tomatoes indoors or else they'll die
overnight. I haven't had any time to think about this, because my
girlfriend moved to the valley I live in, and we've been doing a fair
bit of gardening (yay).

Thanks for the stimulating dialogue, folks.

Paul d'Aoust