Permaculture Database

[jedd <jedd@progsoc.org>]

On Fri Mar 20 2009, Paul d'Aoust wrote:
> Sometimes, when I wake up in the morning, the first thought in my
> head will be 'PCDB!' and my mind will start racing about how to
> construct entity relationships that are flexible enough to model
> everything from actinomycetes to lakes to chickens to exotic hand
> tools, yet structured enough to have useful searches performed on
> them... it's tough work, as I think we all discovered. I don't know
> how many E-R diagrams I've scribbled out on pieces of paper that I've
> subsequently lost.

 Okay - an easy question to follow from this.

 I have an organism table - it contains the taxonomy references,
 amongst other things, and is tied against 20+ taxa tables.  But
 it'll work I think.

 I'm currently stuck on two things.

 The first is non-organisms.  Apart from coming up with a word to
 describe everything that isn't an organism, and debating whether
 to make organisms a subset of a bigger category, is the question
 of whether to break them up into certain categories again, or
 try to keep them totally abstract - ie, to cover everything that
 isn't alive.

 I'm thinking of things here like soil (types), mulch, light/shade,
 water, irrigation pipe, sheds, tools, fertilisers, etc.


 The second thing is relationships.  I want to be able to define a
 relationship between any two things - organic or otherwise - and
 so I'm trying to keep this abstract, flexible, and useful.  At the
 moment I'm thinking that I need to cover two broad aspects of
 the relationship between items.  First, structural - that one item
 is a subset of another.  This is relatively straightforward by a
 component_of table that just has parent and child FK's back to
 the organism/non-organism tables.  The second is the harder
 one, and concerns the type, or degree, or quality of the relationship.

 I'm thinking of things like 'provides', 'requires', 'works with',
 'dislikes', 'prevents' - as the direction or quality of the relation,
 and then perhaps a -10 to +10 quantity to assign to that
 relationship.  I have (yet another) sub-problem handling whether
 that degree or quantity should be variant based upon a use or
 other factors (soil types, climate, site, aspect, etc).

 So .. where did you get up to with this stuff?  Did you head down
 this mental path already and discard it, or have you got some
 hints on where I can take this?

 Jedd.