Skip to Content.
Sympa Menu

pcdb - Re: [pcdb] jedd's requirements

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] jedd's requirements
  • Date: Sat, 31 Mar 2007 11:06:25 +1000

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.




Archive powered by MHonArc 2.6.24.

Top of Page