Permaculture Database

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

Sean, your thoughts on hierarchy are real food for thought. I hafta
admit it's painful to stretch my brain around a design perspective that
I don't naturally think about!!

I've always designed databases with at least one hierarchical table,
because trees were useful for the things I was designing. However, they
did always bother me a bit -- things a table linking to itself, as you
mention -- so I'm certainly open to ideas that would not require the use
of a hierarchy.

> You can't just search for records of a given attribute, you must join
> the table to itself an unknown number of times.  In the best of cases,
> one level of lineage, you have 8000 times 8000 records where you had
> just 8000 before.

well, the stuff I wrote was pretty simple. It'd only ever be dealing
with one object at a time, so any SELECT statement would only grab the
record whose 'objectID' was the same as the current object's 'parentID'.
I never got into complex joins. Although I can sorta see that, in a
database like this, where searching is so important, you would have to
create fairly complex statements to deal with recursion.

> If you enforce the hierarchy (avoid the self join through normalizing
> into a hierarchy of tables), you then face snow flaking to get into
> your 8000 records.

If I understand you correctly, a fully normalised hierarchy would only
have a finite number of levels, right?

> What is the specific justification to demand the use of a hierarchy?

To me, there would be three specific justifications for using hierarchy
in a PCDB:

     1. Inheritance of attributes and connections, to save time with
        data entry (e.g., 'nitrogen fixing' is inherited by all
        leguminosae)
     2. easy attachment of cultivar-specific nodes under a species node
     3. logical organisation of a fairly mind-boggling array of objects

Now, these don't demand the use of a hierarchy, but they suggest it. If
our needs can be met even better with a different structure -- say, the
popular Web 2.0 method of flat structure and lots of 'tags' -- I'm all
for it.

This would certainly remove our need to discriminate things that are
necessarily vague and 'non-discrimating', as you observe. It would save
a headache trying to figure out an appropriate taxonomic tree that
encompasses The Entire World And All Its Elements.

I'd be interested in hearing your suggestions on how something like this
would work. I was thinking about a non-hierarchical approach at the
beginning, but threw it out because the hierarchical approach had some
ideas that I liked. I'm becoming attracted to the former, again, but I
can't exactly picture what it would look like. For instance, would
soils, bioregions, plants, and tools be part of the same generic object
table (flexibility), with plenty of tags to define their functions, or
would they each have their own specific table (specificity)?

Paul d'Aoust