Skip to Content.
Sympa Menu

pcdb - Re: [pcdb] PCDB/Wiki/Guild Database

pcdb@lists.ibiblio.org

Subject: Permaculture Database

List archive

Chronological Thread  
  • From: jedd <jedd@progsoc.org>
  • To: pcdb <pcdb@lists.ibiblio.org>
  • Subject: Re: [pcdb] PCDB/Wiki/Guild Database
  • Date: Sat, 21 Jul 2007 18:02:38 +1000

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




Archive powered by MHonArc 2.6.24.

Top of Page