Permaculture Database

[jedd <jedd@progsoc.org>]

 Hi Lonnie,

> I disagree that the reasons for always using guids or auto-inc
> fields rather than text for the primary keys are resolved or obvious.
> I understand the problems with text.  But the value of using a
> descriptive primary key rather than an arbitrary number is both
> conceptual and practical.

 Okay .. going back to your guild and plant 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...]  )

 The name and unique ID should exist somewhere in its own table,
 with the description held elsewhere.  Additional attributes also are
 held elsewhere.  Do we agree so far?

 Having a descriptive PK is handy for humans, but computers don't
 need them.  I see a significant cost in designing human-readability
 in at this level - in size, performance, and added complexity.

 Let's delve into a real world example and see how it fits.

 I've never done it, but seemingly everyone else grows this 3 sisters
 (a bunch of rocks up in the mountains, for us Sydney-siders)
 arrangement of plants.

 What would the guildName of this item be?
 What would the description of this item be?
 What other additional attributes would you anticipate being in there?

 When the guild is extended, what happens to the description at
 that point, and what happens to references elsewhere that refer to
 a guild of what used to be three items, but which now contains four?

 How does your guild design (including the guildElement table you
 describe elsewhere) deal with multiple varieties (I've got about a
 dozen of each) of maize, corn, curcubits and beans.


 Briefly returning to PK's as text -- I think it pushes redundancy into
 the design way earlier than we'd otherwise be forced to do so,
 I think it limits us over time as we end up with legacy data (which
 we'll have anyway, I know) but with embedded legacy aspects in
 the actual keys rather than the design - and design is easier to
 modify globally / programmatically.  The real world connection that
 exists between the relationship and the entities involved, as you
 observe, is very useful, but I am convinced this connection needs
 to be maintained outside of those tables - partly for reasons of
 normalisation, mostly for reasons of flexibility.

 Using a botanical name (I just can't believe the Romans spoke solely
 in nouns) as a key is (as has been observed elsewhere) very limiting
 as plants are a subset of entities .. or rather, living entities are a
 subset of all-entities.  Having separate stores of data to describe
 and index plants as distinct from, say, mulch, stones, water (would
 they all get their own tables, or would there be a non-plantandanimal
 table?) kind of misses the point of Pc, to my mind, insofar as it
 discounts the interaction of every element, not just certain sets
 of them.

 Yes, some elements are used by most or all things - water, as the
 obvious example - but that's okay.  When I do a search on the
 requirements of the three sisters, it's not so much that I'll be
 amazing to learn that I need water in order to grow them, rather
 I'd be expecting to find out how much of the stuff I need in an
 environment that closely matches mine (daylight hours and intensity,
 soil type, etc).
 
> I've had to clean up data in many many situations where the database
> designers set up good relational datases using integers as primary keys.
> They were full of cases of the same patient, for example, having been
> entered multiple times by multiple users, which the database allowed
> because the primary key didn't really identify (in the real world) the
> entity in question.

 Undoubtedly this occurs.   What happens in cases where the patient's
 name is the PK, and the name is mistyped - you get two records that
 match the same physical human.  I think this is a data validation
 issue - the form that you enter data into would do soundex (f.e.)
 lookups against most of your data and offer up any near hits that
 it finds before committing your data into the system.

 (Meta-posting aside wrt the following -- if you're using the gmail
 client, I'm not sure how you're *not* getting the quotation thing
 working properly.  I can't find any setting in there to turn off the
 default 'insert a > at the start of each line' feature.)

> You said:
>
> When you say an additional layer of abstraction on top of something
> that you assert is already very generic .. how does this scale?  Would
> it be better to have an underlying layer of very specific data and
> do the 'conversion to generic' progammatically, as needed?
>
> Maybe.  I think we'd agree that tracking every last detail of every last
> variation that people devise is not practical.  I think initially we'd like
> to have a list of generic guilds without too much detail.

 Okay.  I think we should be tracking every last detail at every
 opportunity, because if someone comes to us with a lot of data
 and we take only 10% of it and generalise it out, we know we'll
 never get that person back again with all that data.

 We also risk storing wrong data simply because we forced that
 person to make generic choices where they wanted to make specific
 selections.

 For example, the guild you're about to mention that involves walnuts,
 let's say (hypothetical) that it only works with the non-black variety
 and we've only got 'walnut' as an entity.  The user enters their data
 in good faith, is vaguely annoyed that they can't nominate variety
 (actually, the black is a different species of Juglans, isn't it? No
 matter) but presses on anyway.

 The next person comes along, seeking insight into what to grow
 with their hackberry, sees that 'walnut' is a good mix - then heads
 off and buys a black one because that's all they could find.  They
 plant them out and it fails to work.

 That person won't revisit us in the future and add information
 about their guild (working or otherwise).  The original person is
 lost, or more importantly their original data are.

 If the system doesn't even let us distinguish between varieties
 then we're opening ourselves up to gross inaccuracies.  The 3sis
 thing, again, f.e. -- there's about a dozen species that come under
 the label 'bean', yes?  Are all items that people call beans equally
 effective (or even just 'positive entities') in a 3sis arrangement?
 Does jicama (yam bean) for example work well in this arrangement,
 given the beans are poisonous and the harvesting of the plant
 requires digging up the plot.

 My point is that there's obviously an arbitrary line to be drawn
 as to how much specificity we allow or encourage, and I think that
 line should be as close to the molecular level as possible.

> Perhaps we could 
> label it guildType.  As an example take the walnut/hackberry guild from
> Gaia's Garden, where Hemenway says
>
> These three plants--walnuts, hackberries, and currants--furnish the
> foundations of the guild.
>
> So information about the guildType could contain that level of detail--the
> walnut/hackberry guild, containing walnuts, hackberries, and currants as
> elements, with functions and yields defined for each element. 

 This touches on my earlier questions to you (above) - sorry about
 that.  My questions still stand, however.

 With this description though, can I ask if you're implying that the
 row of data in the guild table that matches would contain:
 guildName:   "Walnut / hackberry"
 description:   "Contains walnuts, hackberries and currants"


> Then 
> someone who wants to build a similar guild in a particular location could
> search the database for guilds of this type including whatever additional
> constraints in terms of climatic zone, soil type, cultivar or variety etc.
> are required. 

 'guilds of this type' -- what is this 'type' that you refer to?

 The problem I see with the above is that the description can easily
 be programmatically created by searching the guildmembers table for
 everything that lives in guild #27, and then doing a print to the
 screen of ("This guild contains " . for $i in $arr (print $i . " , "))

 The description is also a superset of the guildname, and in fact
 the guildname is misleading here because it implies that there's
 no currants in this guild.

> If such guilds were in the database, the user would get a 
> list of guilds meeting his requirements, which he could use as a starting
> point for his own design.

 Okay .. this could be very interesting.

 Tell me how the user would search in order to get the information,
 as in what would they have typed in and clicked on to get them to
 this location.  What other information would be presented to them
 based on their specific query.  V.basic screen layouts are good
 tools I think here.


> Tracking plant-to-plant relationships outside of some limiting structure
> like a guild would create complexity that grows factorially as the plant
> list grows.

 I think we'll have space to do this.

 And I think we need to model the complexity of the Real World as
 much as possible, as history shows that trying to force the world to
 fit arbitrary models is rarely satisfying.

 Jedd.