Permaculture Database

[jedd <jedd@progsoc.org>]

 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.