Skip to Content.
Sympa Menu

pcdb - Re: [pcdb] jedd's requirements

pcdb@lists.ibiblio.org

Subject: Permaculture Database

List archive

Chronological Thread  
  • From: "Lonnie Brown" <brownlonnie@gmail.com>
  • To: pcdb <pcdb@lists.ibiblio.org>
  • Subject: Re: [pcdb] jedd's requirements
  • Date: Fri, 30 Mar 2007 10:28:08 -0600

Hi Jedd,

I agree that the plant database has to be incorporated into the guild database because of the issues you mentioned.   Also, the data-entry/data-cleanup side of the project is non-trivial, to say the least.

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.  It gives the table's row identifier a real-world relationship to what it's identifying, thereby eliminating the possibility of duplicating data when many people are involved in doing the data-entry.  For example, the pfaf database uses auto-inc fields for primary keys throughout.  It also has a field for the Latin Name of each plant.  But the latin name uniquely identifies each plant.  Using the latin name as a primary key eliminates one level of indexing and makes the data more transparent to someone like me, who is trying to figure out how the plants in the cultivars table, for example, are associated with the plants in the latin_name table for the purposes of importing the pfaf database into a guild database.  Most of the primary keys in the various tables aof the pfaf db are simply called id.  So as the pfaf database stands it is difficult to bring the data into the larger guild database while keeping that relationship intact. 

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.

This may be a philosophical difference, but in my opinion, if there is a system in place that uniquely identifies different entities of a given type (like the latin name for plants), or if the number of entities is probably not going to number in the tens of thousands and you can think of a systematic naming convention, it's better to use a code or description that will identify each entity in a more transparent way. 

In short, while using integers as primary keys may provide improvements in performance, it doesn't model real-world entities. 

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.  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.   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.  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.  If his design turns out to be significantly different or adds something new or unique to the knowledge of that guild type, then his guild could become a part of the database.

If no instances of a guild type exist for his search constraints, he could relax the constraints and search for all guilds of that type.  He could then use those results as a starting point for his own research.

These more specific guild instances would be tracked in the table structure I described in my previous post.  Of  course the guildType would be a foreign key in the guild table.  

You wrote:

Third, this design seems to be self-limiting -- plant-to-plant r'ships
need to be tracked, of course, but both positive and negative
influences between different plants should be trackable (rather
than having multiple tables, some showing positive r'ships, others
showing negatives, others showing strongly pos and strongly neg,
and so on).

I'm proposing that the guild is the natural way to track plant to plant relationships.  Characteristics such as whether a plant is allelopathic, has a broad, shallow, deep, or narrow root structure, etc, are attributes of plants that are tracked in the plant database section, though they could be incorporated as attributes in the guildElement table if they play a functional role in the guild. 

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

You wrote:

Herein may lay the quintessential difference between our views.

I think it's :
a) very important to design flexibility from the outset, as it's nearly
     impossible to increase the flexibility of a system once it's in
     production, and
b) pretty much impossible to determine what level of complexity is
     necessary at the design stage, particularly with systems that are
     not well spec'd (screen mock ups would go a long way here) and
     whose functionality requirements are almost definitely going to
     grow over time.

I agree with point a) wholeheartedly, with the caveat that there are limitations to what a database can do.  I think that if you have a good data model, then you can create a database that is functionally very flexible.  The data model has to incorporate everything you want to know about the entities you are tracking.  For some people screen mockups may be helpful in thinking through what they want to know. 

If you want functionality that exceeds what a relational database can provide, then the development process really becomes a challenge.  Alternate technologies might include semantic webs or expert systems. 

To me, the idea is to share what little is known about guilds and guild design and provide a way for new discoveries to become a part of a growing canon. 

best regards,

Lonnie

On 3/30/07, jedd <jedd@progsoc.org> wrote:
Hi Lonnie,

I'm not sure what you did, but gmail includes a quotation feature so
that you (by which I mean me) can easily distinguish which bits
of a message are quoted, and which bits aren't.

On Saturday 31 March 2007 12:02 am, Lonnie Brown wrote:
> A guild is an entity that exists at a higher level of abstraction than a
> plant.  As such plants, can function as elements of a guild, and a plant
> database serves as a set of lookup tables for a guild database.

I'm reasonably hip to db design.

What I don't understand is the delineation between the plant database
and the permaculture database.  If the latter relies on the former for
table lookup (I'm not sure this qualifies them as separate db's by
definition) then the designers of the latter either sit around and
wait for the former to be written and the API & schema published,
or they are involved in writing both.  If the same set of people
are writing both systems, then you've just extended the time it'll
take to develop either by a factor of something greater than 2.

Having them physically separate seems to just be asking for trouble
(availability and integration for starters).  My question relates more
to the design process and ultimate intent, rather than a 'this data
sits in this table, and that data sits in that table' issue.

> Having thought this far, we can begin to sketch out a data model and draw
> an E-R diagram for the Guild database.

My requirements extend far beyond either a database of facts about
plants, and/or a database of facts about relationships between those
plants.

Can some other people pipe up on this subject please -- what are
the ultimate expectations of this (the larger of the two) systems?

Lonnie - this obviously includes you - can you describe things in
terms of how you anticipate interacting with this system, more so
on the getting data out of the system (what you're looking for, how
you might interact with the system to do it, than adding information
into the database I mean).

> Additionally, we want to track
> information (for reference and lookup purposes) about plants.  The pfaf
> database provides a good starting point for the plants portion of the
> database.

There's lots of good oil in the pfaf db, however the data cleansing
activities required to get the data in good order are non-trivial, and
I doubt (judging by the presentation method) that the underlying data
are ready to integrate into a separate system.  Much of the data
appears (from the outside looking in, admittedly) to be embedded
in variable length generic text fields.

> CREATE TABLE guild (
>     guildName    varchar(20)    NOT NULL     PRIMARY KEY,
>     description    text        NOT NULL,
>     [additional attributes...]
> );

I'm not going to go through each of the tables you're proposing in
detail, but will comment on this one (and extrapolate a tad).

First, you definitely need guids or auto-inc fields rather than
text for the primary keys .. for obvious reasons.

Second, the description is redundant, as it will be acquired and
generated from other fields - specifically whichever rows in the
plant.table that points back to membership of this guild.  Basic
normalisation activities preclude the maintenance of a description
field here that can (and should) be so generated.  It's conceivable
that a guild name (itself a type of description) may be pointless
at all, and consequently this table also, as the relationship (and its
description) can be generated by selects from the plant and r'ship
tables.

Third, this design seems to be self-limiting -- plant-to-plant r'ships
need to be tracked, of course, but both positive and negative
influences between different plants should be trackable (rather
than having multiple tables, some showing positive r'ships, others
showing negatives, others showing strongly pos and strongly neg,
and so on).

IOW the abstraction levels required are huge to the point of mind-
wobbling (for me) and probably *should* be approached once we've
got some common ground about entity attributes, simply because entity
attributes are something more people can think about, and because
they're easier things to think about .. and in no small part because
they'll limit / dictate / enlighten the discussion about r'ships
between them, IMO.

Your references to a hierarchy of tables:

> The guild table is the parent table.  The element table is the
> child of the guild, and the function and yield tables are children of the
> element table.

... implies some confusion about how relational databases work,
unless I've misunderstood what you mean by the parent / child
references.  Can you perhaps explain further what you're trying
to describe with those terms?

> This structure might have to be expanded to account for climatic and
> regional variations.  This could be done by adding an additional layer of
> abstraction underneath that proposed above.  You would use the structure
> described above to track the essential elements of a guild in their most
> generic form.  Then you would add tables with a similar relational
> structure for tracking guilds in more specific areas.

I'm not sure I understand how you anticipate this would work.

Would there be multiple rows for 'peach' depending on where I
was growing my peach and you were growing yours?  How does
your scheme handle such duplicates in this case?

How do we handle the fact that my Coronet variety and your Supersweet
variety, are both the same 'type' of element, and almost definitely
have comparable guild attributes, but may have substantially different
climatic requirements (chill hours, f.e.) and yields/outputs?

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?

Removing specificity of data is obviously a one way street, and
there seems to be little benefit in doing so given my gut feel on the
size of data I'm thinking about and the power of modern tech.  Worst
case you could of course separate out your olap data to a different
system if performance was ever an issue .. but I see that as a very
long way down the track.

> In any event, I think it's best not to design unnecessary complexity into
> the database.

Herein may lay the quintessential difference between our views.

I think it's :
a) very important to design flexibility from the outset, as it's nearly
     impossible to increase the flexibility of a system once it's in
     production, and
b) pretty much impossible to determine what level of complexity is
     necessary at the design stage, particularly with systems that are
     not well spec'd (screen mock ups would go a long way here) and
     whose functionality requirements are almost definitely going to
     grow over time.

cheers,
Jedd.
_______________________________________________
pcdb mailing list
pcdb@lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/pcdb




Archive powered by MHonArc 2.6.24.

Top of Page