Permaculture Database

["Lonnie Brown" <brownlonnie@gmail.com>]

On 3/30/07, jedd <jedd@progsoc.org> wrote:

 I'm still not sure how the permaculturedb and plantdb's goals
 differ -- though I use plants (below) as examples (I refused to
 succumb to peer pressure and talk about chooks!) every item
 in the landscape (as well as the non-physical items) need to have
 a presence in the database.  It seems to me that a plantdb would
 necessarily be a subset of the pcdb.  And/or having both implies
 much duplication of effort.


A plant database is a plant reference listing plants, their climatic, soil, and water requirements, and their uses.  Its purpose is to collect information about as many plants as possible in a single location in order to simplify the task of doing research.

A guild database is a guild reference with the same general purpose with respect to plant guilds as the plant database has with respect to plants.  A guild is a system composed of many elements.  These elements include plants, animals both domestic and wild, insects, soil microbes, etc.  Elements interact with one another and with we human guild designers in multiple ways.  These interactions can be tracked as functions and yields, yields being functional byproducts that explicitly benefit humans.  

A guild is an entity that exists at a higher level of abstraction than a plant.  As such plants, can function as elements of a guild, and a plant database serves as a set of lookup tables for a guild database.

Having thought this far, we can begin to sketch out a data model and draw an E-R diagram for the Guild database.  

The basic entities that we wish to track information about are Guilds, elements, functions, and yields.  Additionally, we want to track information (for reference and lookup purposes) about plants.  The pfaf database provides a good starting point for the plants portion of the database.  

For now, let's not worry about defining all the attributes of every entity. Instead, let's layout the table structure for the entities and their relations.

Rather than try to draw an ER diagram in ascii format, I'll just write the sql code to create the necessary tables, and then provide some discussion of how this basic structure might be expanded.

Basic Entity Tables:

CREATE TABLE guild (
    guildName    varchar(20)    NOT NULL     PRIMARY KEY,
    description    text        NOT NULL,
    [additional attributes...]
);

CREATE TABLE element (
    element        varchar(20)    NOT NULL    PRIMARY KEY,
    description    text        NOT NULL,    
    [additional attributes...]
);

CREATE TABLE function (
    function    varchar(20)    NOT NULL    PRIMARY KEY,
    description    text        NOT NULL,
    [additional attributes...]
);

CREATE TABLE yield (
    yield        varchar(20)    NOT NULL    PRIMAY KEY,
    description    text        NOT NULL,
    [additional attributes...]
);

Relational Tables:

CREATE TABLE guildElement (
    guildName    varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES guild (guildName),
    element        varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES element (element),
    description    text,
    [additional attributes...],
    PRIMARY KEY guildElement_PK (guildName, element)
);

CREATE TABLE functionPerElement (
    guildName    varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES guild (guildName),
    element        varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES element (element),
    function     varcahr(20)    NOT NULL
        FOREIGN KEY REFERENCE function (function),
    description    text,
    [additional attributes...],
    PRIMARY KEY elementGuildFunction_PK (guildName, element, function)
);

CREATE TABLE yieldPerElement (
    guildName    varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES guild (guildName),
    element        varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES element (element),
    yield        varchar(20)    NOT NULL
        FOREIGN KEY REFERENCES yield (yield),
    [additional attributes...],
    PRIMARY KEY    elementGuildYield_PK    (guildName, element, yield)
);
    
The field lengths given in the above description are somewhat arbitrary and could be changed.  The structure described is quite flexible and resolves any many-to-many relationships among guilds, elements, functions, and yields.  The guild table is the parent table.  The element table is the child of the guild, and the function and yield tables are children of the element table.  There can be arbitrarily many elements in each guild, and any element may be a member of more than one guild.  Each element can have arbitrarily many functions, and any function can be performed by arbitrarily many elements.  Likewise for elements and yields, at least as far as the structure of the database is concerned.

With some minor alterations in structure, the plants for a future database would plug directly into the guild database as an element lookup and general reference.  The pfaf structure would be integrated into this structure, allowing structured queries that would generate lists of potential elements (plants, mainly) with prescribed requirements, functions, and yields.  

This structure might have to be expanded to account for climatic and regional variations.  This could be done by adding an additional layer of abstraction underneath that proposed above.  You would use the structure described above to track the essential elements of a guild in their most generic form.  Then you would add tables with a similar relational structure for tracking guilds in more specific areas.

For example peach guild, zone 6; or peach guild, middle rio grande valley... Whatever system seems to work best.  

In any event, I think it's best not to design unnecessary complexity into the database.  This is not a dynamic model of a real-world system; it's only a reference, a starting point for people who are trying to learn to apply permaculture principles to securing local food supplies and who may not have the time or money to do extensive botanical and ecological research.

I don't think the database should be seen as a panacea that can automate the process of guild design, since in that capacity it would turn out to be a dismal failure.

Individual copies and synchronization:

Individual copies of the database could be downloaded as sql or xml files (to allow for total platform independence) or distributed (probably have to be sold in this format) as applications on cd (for those who don't have the time or background to be do-it-yourselfers in this area).

Additions to the individual applications could be posted (this process could be automated for the plug-and-play users) as xml files and imported into the main database after some type of review process.

Maintenance of this system once it has been set up would require ongoing committment from a small team which would include a dba, a web programmer, some administrative person(nel), and a peer review committee. 

Lonnie

On 3/30/07, jedd <jedd@progsoc.org> wrote:

I'm still not sure how the permaculturedb and plantdb's goals
differ -- though I use plants (below) as examples (I refused to
succumb to peer pressure and talk about chooks!) every item
in the landscape (as well as the non-physical items) need to have
a presence in the database.  It seems to me that a plantdb would
necessarily be a subset of the pcdb.  And/or having both implies
much duplication of effort.

Consider a particular plant.  I'd want to know:
o  Climate preference
o  Climate tolerances
o  Size (optimum)
o  Size (variable based on climate)
o  Soil preferences ... tolerances
o  Water requirements ... tolerances
o  Sun/shade requirements ... tolerances
o  Functions
o  Warnings
o  Companions
     -  sliding scale, say -10 to +10
     -  textual caveats for any cited companions
     -  climates where certain companionships will or won't work
     -  obviously there's a conceptual 1:n relationship here
o  Herbal / medicinal uses (perhaps a sub category of functions)
     - recipes
     - doses
     - contraindications
o  Food potential
      - as above
(Union of those two:
      - containers / materials that can and can't be used in the
        preparation - discolouration warnings, spoilage, corrosion ...)
o  Weed potential
      - impact
      - seasonal / microclimate / weather / soil / etc influences
      - methods of control
o  Pest potential
      - impact
      - seasonal / microclimate / weather / soil / etc influences
      - methods of control
o  Propagation methods
o  Fertilisation requirements .. tolerances
o  Varieties (links to other instances, with replication of the
     common bits to this specific variety - this can get messy as you
     want to distinguish between specific varieties that may have
     very different medicinal properties but share pretty much every
     other property - contributors will not update both sets of data,
     guaranteed).
o  Performance - different metrics and under different conditions
     - comparison performance early, mid, late season
     - correlation to weather for each year that perf data is provided
o  Dimensions (and how they vary based upon the above)
o  What it looks like (ie, pictures)
     - up close
     - just after germination (each week snapshots) for identification
     - flowers
     - each pic can contain links to information about climate,
       fertiliser, timings, location, soil types, etc where it was
       grown (obviously this would come out of a person's profile,
       unless that person wasn't registered with the system (but still
       wanted to add that information in) and/or deal with time-
       dependency (an utter nightmare in db's) if that person's moved
       since the picture was taken or took it at a site other than
       their registered and annotated home site
o  Yield potential under each permutation of the above