Permaculture Database

[jedd <jedd@progsoc.org>]

 Paul - just found your note in the Discussion page attached to my
 community_portal page that I posted a while back.  To answer your
 question, I don't mind, so long as there's an easy way for me to work
 out what bits are your comments.  Not so much for attribution, but
 just so I know which bits to read.  Tracking conversations in a wiki
 is always a painful business -- compare and contrast email -- but I
 think I've asserted this truth several times previously.

 Anyway, should I be adding the idea of a project driven, collaborative
 custom built database to my list of Nice Ideas That Never Made It?

 I'm resolved to writing my own system, form scratch, and having
 it customised to my own purposes, with regrettably lower priority
 for making it easy to navigate and contribute to for non-Jedd types,
 but such a system would still be infinitely more useful to me than
 a wiki that contains a bunch of free form text only.

 I think I've asserted this a few time previously, too.

 Such a wiki approach could possibly be very useful, iff everyone
 used it, and no one used any other wikis out there .. you end up
 with something pretty useflu (a la wikipedia) but with world+dog
 setting up non-comparative, standalone, non-relational, loose, text-
 based systems .. it's not a particularly compelling path to follow.

 On a slightly more upbeat note, and while fully acking that the
 problems I talk about towards the end of my permaculture.info page
 are almost totally unattended to ... I'll attach below the schema
 that I've set up so far.  I was looking at some GUI versions of same,
 but they're pretty thin on the ground on GNU/Linux, and ultimately
 you end up having to either take your design changes back to the
 GUI, or do it all from there as you go, and neither is appealing.

 Again, I say, this is very far from complete, but gives an inkling to
 any that may have read this far (through the list's history, I mean,
 not just this message) about my intent.  Postfixed some test data
 that may add meaning - but it's pretty rusty and I haven't updated
 that as often as the schema.  Note, tabstops set to 4.

 Jedd.



# CREATE schema
#
# Set up all tables within the database.

# Types used below (taken from MySQL's standards)
# bigint (8 bytes)    unsigned max = 18,446,744,073,709,551,615
# int (4 bytes)       unsigned max =              4,294,967,295
# mediumint (3 bytes) unsigned max =                 16,777,215
# smallint (2 bytes)  unsigned max =                     65,535
# tinyint (1 byte)    unsigned max =                        255
# 
# tinytext   = 255 characters
# mediumtext = 16,777,215 characters
# longtext   = 4,294,967,295 characters
#
# SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

# pending problems to resolve:
#       o  each person has primary soil type, but probably has many
#               soil types that they own or work on / with.  locations
#               with a different type.  once a type is set, it's cast
#               in stone (due to people:soil == n:m ratio) and cheapness
#               of bigints.
#       o  each person has primary climate and multiple microclimates,
#               and microclimates need separate table obviously.

DROP DATABASE permadb;
CREATE DATABASE permadb;
GRANT ALL on permadb.* to 'jedd'@'%';
USE permadb;



CREATE TABLE taxa_kingdom  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_phylum  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_subphylum  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_class  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_subclass  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_order  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_suborder  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_family  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_subfamily  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_genus  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_subgenus  (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_species (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );
CREATE TABLE taxa_subspecies (
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        INDEX (name),
        PRIMARY KEY (id)
        );



CREATE TABLE scinamesyn (               # botanical / scientific name 
synonyms - only one 
is valid
        # anything in this table is a historical name only - the valid 
current name 
is in the organism table.
        # JEDD - previously used oldfam, oldgen, newfam, newgen - etc - but 
complicates arose with that
        #               approach, and instead the organism.id's position as 
canon is reaffirmed 
here.  Uniqueness
        #               is based on arbitrary id only (good for 
change_history) rather than PK of 
6 fields.
        #               This approach elegantly handles 1:n ratio of current 
to historical 
botanical names.
        id                                      SERIAL,                       
          # mostly unused, but definitely unique
        organismid                      bigint,                               
  # to organism.id - from where we pick up 
fam/gen/spe/etc
        old_taxa_kingdom        bigint,                                 # to 
relevant table.id
        old_taxa_phylum         bigint,                                 # to 
relevant table.id
        old_taxa_subphylum      bigint,                                 # to 
relevant table.id
        old_taxa_class          bigint,                                 # to 
relevant table.id
        old_taxa_subclass       bigint,                                 # to 
relevant table.id
        old_taxa_order          bigint,                                 # to 
relevant table.id
        old_taxa_suborder       bigint,                                 # to 
relevant table.id
        old_taxa_family         bigint,                                 # to 
relevant table.id
        old_taxa_subfamily      bigint,                                 # to 
relevant table.id
        old_taxa_genus          bigint,                                 # to 
relevant table.id
        old_taxa_subgenus       bigint,                                 # to 
relevant table.id
        old_taxa_species        bigint,                                 # to 
relevant table.id
        old_taxa_subspecies     bigint,                                 # to 
relevant table.id
        deprecated                      datetime,                             
  # when its usage was deprecated
        comment                         tinytext,                             
  # why & when it was replaced
        create_who                      bigint,                               
  # user-id who posted this synonym
        create_when                     datetime,                             
  # when it was posted
        INDEX (organismid),
        INDEX (oldfamily),
        INDEX (oldgenus),
        INDEX (oldspecies),
        PRIMARY KEY (id)
        );


CREATE TABLE varnamesyn (  # variety name synonyms - typically many names of 
varieties are valid
        # do we need weightings in here - the *most* common variety name .. ?
        id                                      bigint UNIQUE,                
  # to organism.id
        varid                           bigint,                               
  # to vardex.id
        comment                         tinytext,                             
  # why & where it's used
        create_who                      bigint,                               
  # user-id who posted this synonym
        create_when                     datetime,                             
  # when it was posted
        INDEX (varid),
        PRIMARY KEY (id)
        );

CREATE TABLE vardex (   # variety name index
        id                                      SERIAL,
        name                            char(99) UNIQUE,
        PRIMARY KEY (id)
        );

CREATE TABLE cnsyn (    # common name synonyms
        # do we need weightings in here - the *most* common name .. ?
        id                                      bigint UNIQUE,                
          # to organism.id
        cnid                            bigint,                               
          # to cndex.id
        comment                         tinytext,                             
          # why & where it's used
        weighting                       tinyint,                              
          # how common this name is - inserted by # of hits?
        create_who                      bigint,                               
          # user-id who posted this synonym
        create_when                     datetime,                             
          # when it was posted
        INDEX (cnid),
        PRIMARY KEY (id)
        );

CREATE TABLE cndex (    # common name index
        id                                      SERIAL,
        name                            char(200) UNIQUE,
        PRIMARY KEY (id)
        );


# any living entity - can and/or will (?) have any of the following attributes
# JEDD - do we need a [taxa_]variety reference in here, or will varieties 
always
# be handled as part of table.varnamsyn -- pros / cons?
CREATE TABLE organism  (
        id                                      SERIAL,
        fam                                     bigint,
        gen                                     bigint,
        spec                            bigint,
        var                                     bigint,
        taxa_kingdom            bigint,                                       
  # to relevant table.id
        taxa_phylum                     bigint,                               
          # to relevant table.id
        taxa_subphylum          bigint,                                       
  # to relevant table.id
        taxa_class                      bigint,                               
          # to relevant table.id
        taxa_subclass           bigint,                                       
  # to relevant table.id
        taxa_order                      bigint,                               
          # to relevant table.id
        taxa_suborder           bigint,                                       
  # to relevant table.id
        taxa_family                     bigint,                               
          # to relevant table.id
        taxa_subfamily          bigint,                                       
  # to relevant table.id
        taxa_genus                      bigint,                               
          # to relevant table.id
        taxa_subgenus           bigint,                                       
  # to relevant table.id
        taxa_species            bigint,                                       
  # to relevant table.id
        taxa_subspecies         bigint,                                       
  # to relevant table.id
        commonname                      bigint,                               
          # to cndex.id
        INDEX (fam),
        INDEX (gen),
        INDEX (spec),
        INDEX (var),
        PRIMARY KEY (id)
        );

# organism-to-organism mapping, describing how to distinguish between similar 
entities.
# APP will show diffs in organism attributes (height, etc), and consult items 
in this
#       table (if org.id present in either .id1 or .id2) to find a verbal 
description.
CREATE TABLE diffdex  (
        id                                              SERIAL                
                  # mostly unused
        id1                                             bigint                
                  # first organism ID
        id2                                             bigint                
                  # second organism ID
        difference                              char(2048)                    
          # text, possibly html-tagged, describing the 
diffs.
        INDEX (id1),
        INDEX (id2),
        PRIMARY KEY (id)
        );


# Specific to plants (probably)
CREATE TABLE companionplanttype  (
        id                                              int AUTO_INCREMENT,   
          # heavily controlled table (admin add only)
        description                             char(99) UNIQUE,              
  # eg. flavour, pest, disease, vigour, shade, 
nitrogen
        PRIMARY KEY (id)
        );


# Specific to plants (probably)
# (but consider animal/plant symbiosis, plant/fungus, plant/bacteria, 
animal/fungus symbiotic r'ships)
CREATE TABLE companionplant  (
        id                                              SERIAL,
        organism_prov                   bigint,                               
  # to organism.id - that provides the benefit
        organism_recv                   bigint,                               
  # to organism.id - that receives the benefit
        companiontype                   bigint,                               
  # to companionplanttypes.id
        weighting                               tinyint,                      
          # effect on scale of -100 to +100?
        report_who                              bigint,                       
          # user-id who posted this relationship
        report_when                             datetime,                     
          # when it was posted
        report_weight                   tinyint,                              
  # how convinced they are / how much research 
they've done
        INDEX (organism_prov),
        INDEX (organism_recv),
        PRIMARY KEY (id)
        );

CREATE TABLE soildex  (
        # soil types can probably only be added rather than modified - maybe 
owner 
can change?  only if no ext refs to this type?
        # flag for unused -- so unusued or accidental types can be purged - 
worth 
doing?
        id                                              SERIAL,               
                  # everyone will have lots of soil types
        description                             tinytext,                     
          # used by contributor - people may 
have 'favourite' soil list
        ph                                              tinyint,
        organicmatter                   tinyint unsigned,               # 
percentage?  Need to be float, or ppm or 
pp1000?
        claysandratio                   tinyint unsigned,               # 
good way of doing ratio - extant metric 
for this?
                # mineral OR elementcontent _N, _P, _K, _Mg and so on .. ?
                # need to come up with ideas on recording this info - 
mineraldex table?
        watercapacity                   tinyint unsigned,               # 
percentage?  extant metric, or holding 
capacity by mass/vol?
        drainagerate                    int unsigned,                   # dig 
a hole, fill with water, count to ... ?
        density                                 tinyint unsigned,             
  # specific gravity, or alternative metric?
        depthtoclaypan                  int unsigned,                   # 
measured in mm?
        create_who                              bigint,                       
          # user-id who posted this synonym
        create_when                             datetime,                     
          # when it was posted
        latitude                                tinytext,                     
          # or separate deg/min/sec into three fields for 
search/matching?
        longitude                               tinytext,                     
          # or separate deg/min/sec into three fields for 
search/matching?
        PRIMARY KEY (id)
        );


CREATE TABLE mulchdex  (
        id                                              SERIAL,
        description                             char(99) UNIQUE,              
  # used by contributor - people may 
have 'favourite' mulch types
        mulchtype                               tinyint,                      
          # to mulchtype.id
        tempdelta                               tinyint,                      
          # temperature delta above/below mulch - at 
particular temps?
        water_feature                   tinyint,                              
  # water stalling / holding features ... somehow?
        PRIMARY KEY (id)
        );

CREATE TABLE mulchtype  (
        id                                              int AUTO_INCREMENT,   
          # heavily controlled table (admin add only)
        description                             char(99) UNIQUE,              
  # straw, bark, none, stone, living, ... 
        PRIMARY KEY (id)
        );

CREATE TABLE climatetype  (
        id                                      SERIAL,
        description                     char(99) UNIQUE,                      
  # used by contributor - people may 
have 'favourite' mulch types
        temp_min                        tinyint,                              
          # temp in C
        temp_max                        tinyint,                              
          # temp in C
        PRIMARY KEY (id)
        );

CREATE TABLE microclimatetype  (
        id                                      SERIAL,
        description                     char(99) UNIQUE,                      
  # used by contributor - people may 
have 'favourite' mulch types
        externalclimate         bigint,                                       
  # to climatetype.id - may be USELESS .. ?
        temp_min                        tinyint,                              
          # temp in C
        temp_max                        tinyint,                              
          # temp in C
        PRIMARY KEY (id)
        );

CREATE TABLE glossary  (
        id                                      SERIAL,                       
                  # Possible this is best in the wiki - but not so portable 
there
        term                            tinytext,                             
          # Word or phrase we're defining
        definition                      char(2048),                           
          # The big definition, including URLs and 
citations as needed
        # JEDD - Need a history component here - possibly maintained in a 
separate 
table of changes, say.
        INDEX (term),
        PRIMARY KEY (id)
        );

CREATE TABLE changehistory  (
        id                                      SERIAL,                       
                  # probably unused
        object_table            tinytext,                                     
  # which table the change occured in
        object_id                       bigint,                               
          # id of object in that table
        change_who                      bigint,                               
          # user id of person who made the change
        change_when                     datetime,                             
          # when the change was made
        description                     char(200),                            
          # if a description summary was provided ...
        item_before                     varchar(4096),                        
  # needs to be as big as the biggest 
table.attribute anywhere else
        INDEX (object_id),
        INDEX (change_who),
        PRIMARY KEY (id),
        );



#######################################################################



#
# INSERT test data
#
# Insert some data for testing - some may be useful for trial installations.

USE permadb;

INSERT INTO
        specdex (name)
        VALUES ("undatus");                                     # pitaya
INSERT INTO
        specdex (name)
        VALUES ("esculentum");                          # marama bean
INSERT INTO
        specdex (name)
        VALUES ("molinae");                                     # chilean 
guava (current)
INSERT INTO
        specdex (name)
        VALUES ("ugni");                                        # chilean 
guava (deprecated)


INSERT INTO
        gendex (name)
        VALUES ("Hylocereus");                          # pitaya
INSERT INTO
        gendex (name)
        VALUES ("Tylosema");                            # marama bean
INSERT INTO
        gendex (name)
        VALUES ("Ugni");                                        # chilean 
guava (current)
INSERT INTO
        gendex (name)
        VALUES ("Myrtus");                                      # chilean 
guava (deprecated)
INSERT INTO
        gendex (name)
        VALUES ("Eugenia");                                     # chilean 
guava (deprecated)


INSERT INTO
        famdex (name)
        VALUES ("Cactaceae");                           # pitaya
INSERT INTO
        famdex (name)
        VALUES ("Fabaceae");                            # marama bean
INSERT INTO
        famdex (name)
        VALUES ("Myrtaceae");                           # chilean guava

INSERT INTO
        organism (fam, gen, spec, var, commonname)
        VALUES ((SELECT id FROM famdex WHERE name="Myrtaceae"),
                        (SELECT id FROM gendex WHERE name="Ugni"),
                        (SELECT id FROM specdex WHERE name="molinae"),
                        NULL,
                        "Chilean guava"
                );

# Two synonyms of Ugni molinae are used - Myrtus ugni and Eugenia ugni
INSERT INTO
        scinamesyn (id, fam, gen, spec, comment, deprecated_when, create_who, 
create_when)
        # JEDD - fix this up - need to sub-select to find famdex.id based on 
famdex.name
        #VALUES ((SELECT id FROM organism WHERE fam="Myrtaceae" AND 
gen="Ugni" AND 
spec="molinae"),
        VALUES ((SELECT id FROM organism WHERE 
                                        fam=(SELECT id FROM famdex WHERE 
name="Myrtaceae") AND
                                        gen=(SELECT id FROM gendex WHERE 
name="Ugni") AND
                                        spec=(SELECT id FROM specdex WHERE 
name="molinae")) ,
                        (SELECT id FROM famdex   WHERE name="Myrtaceae"),
                        (SELECT id FROM gendex   WHERE name="Myrtus"),
                        (SELECT id FROM specdex  WHERE name="ugni"),
                        NULL,
                        NULL,
                        NULL
                );



INSERT INTO
        scinamesyn (id, fam, gen, spec, comment, create_who, create_when)
        VALUES ((SELECT id FROM organism WHERE 
                                        fam=(SELECT id FROM famdex WHERE 
name="Myrtaceae") AND
                                        gen=(SELECT id FROM gendex WHERE 
name="Ugni") AND
                                        spec=(SELECT id FROM specdex WHERE 
name="molinae")) ,
                        (SELECT id FROM famdex   WHERE name="Myrtaceae"),
                        (SELECT id FROM gendex   WHERE name="Eugenia"),
                        (SELECT id FROM specdex  WHERE name="ugni"),
                        NULL,
                        NULL,
                        NULL
                );




INSERT INTO
        cndex (name),
        VALUES ("Chilean guava");
INSERT INTO
        cndex (name),
        VALUES ("Murtilla");


INSERT INTO
        cnsyn (id, cnid, comment, weighting, create_who, create_when)
        VALUES ((SELECT id FROM organism WHERE 
                (SELECT id FROM organism WHERE fam="Myrtaceae" AND gen="Ugni" 
AND 
spec="molinae"),
                (SELECT id FROM cndex WHERE name="Chilean guava"),
                NULL,
                NULL,
                NULL,
                NULL,
                );
INSERT INTO
        cnsyn (id, cnid, comment, weighting, create_who, create_when)
        VALUES ((SELECT id FROM organism WHERE 
                (SELECT id FROM organism WHERE fam="Myrtaceae" AND gen="Ugni" 
AND 
spec="molinae"),
                (SELECT id FROM cndex WHERE name="Murtilla"),
                NULL,
                NULL,
                NULL,
                NULL,
                );

INSERT INTO
        companionplanttype (description)
        VALUES ("flavour");
INSERT INTO
        companionplanttype (description)
        VALUES ("vigour");
INSERT INTO
        companionplanttype (description)
        VALUES ("pest control");

INSERT INTO
        glossary (term, definition)
        VALUES (umbel, "A kind of flower cluster in which the flower stalks   
  radiate 
from a common point, as in the carrot and milkweed. It is simple or compound; 
in the latter case, each peduncle bears   another little umbel, called 
umbellet, or umbellule.");
INSERT INTO
        glossary (term, definition)
        VALUES (bulbil, "A small or secondary bulb; hence, now almost 
exclusively: An 
aerial bulb or deciduous bud, produced in the leaf axils, as in the tiger 
lily, or relpacing the flowers, as in some onions, and capable, when 
separated, of propagating the plant; -- called also bulblet and brood bud.");
        


# Use elephant garlic
# Allium ampeloprasum - common or basic form is elephant garlic
# var: Pearlzwiebel
#               aka multiplier leek, argentine garlic
#               diff similar to elephant but it produces a cluster of solid 
spherical
#                        white bulbs or rounds and never produces cloves.  
The bulbs can
#                       be distinguished from onion bulbs because they are 
solid, not layered.
#
# var babingtonii
#               aka British leek, Welsh leek
#               has flowers and bulbils in the umbel but otherwise resembles 
elephant 
garlic
#
# var bulbiferum
#               very similar to var babingtonii but has smaller bulbils
#               found in Guernsey and France
#
# Allium giganteum
#               diff similar to elephant garlic but easily distinguished by 
the breadth of
#               the leaf and the colour of the flower (chap 11 of Garlic & 
Friends)
#
#CREATE TABLE diffdex
#       id                                              SERIAL                
                  # PK probably ignored
#       id1                                             bigint                
                  # first organism ID
#       id2                                             bigint                
                  # second organism ID
#       difference                              char(2048)                    
          # text, possibly html-tagged, describing the 
diffs.
#       INDEX (id1),
#       INDEX (id2),
#       PRIMARY KEY (id)

##varieties
# INSERT INTO
#       vardex (name)
#       VALUES ("big red type");



##companion plants
#INSERT INTO 
#       companionplant (organism_prov, organism_recv, companiontype, 
weighting,
#                                       report_who, report_when, 
report_weight)
#       VALUES ( ... );


# Need to add test data for
#      soildex
#      mulchdex
#      mulchtype
#      climatetype
#      microclimatetype