Permaculture Database

[Sean Maley <semaley@yahoo.com>]

----- Original Message ----

From: Lonnie Brown <brownlonnie@gmail.com>
To: pcdb <pcdb@lists.ibiblio.org>
Sent: Friday, March 30, 2007 12:28:08 PM
Subject: Re: [pcdb] jedd's requirements

> I disagree that the reasons for always
> using guids or auto-inc fields rather than
> text for the primary keys are resolved or
> obvious.

What happens when you find that embarrassing typo after all of the fields have been populated and everyone is clamoring to enter data?  Now you have to go to each table with that varchar typo and lock everyone out while you effectively update the entire data set (each table with the field gets a lock).  Additionally, an integer would be far better to use in a join than several integers, depending upon how many characters you allowed.  I won't take a database seriously without such fundamentals taken into consideration.

> 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.

Then pick a descriptive meta-data name.  Data entry and tiny data sets allow these design flaws.  However, a large data set and analytical usages make a big headache for resource management (the problem emerges in an exponential manner as the data set grows).  For the same reasons, OLAP usage of data pulls the data model away from normalization (into star schema, etc).  The PFAF data gets away with things that wouldn't be tolerable when applied to the same kind of model for insects (over a million species).

> Using the latin name as a primary key eliminates
> one level of indexing

This is a misunderstanding of data modeling verses indexing.  You get exponentially better performance through good data modeling, which indexing is only a small part.

> 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 of 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.

You are correct.  The PFAF database has issues.  It is not acceptable to name a field 'id' in my book.  At the same time, I must admit to this being a common practice.  I would prefer pfaf_uid, so I know I'm dealing with a specific data set.  In a combined data set, I'd think the ITIS number would be better, since PFAF only deals with plants and we need to combine all species.

> which the database allowed because the primary key
> didn't really identify (in the real world) the entity in
> question.

This isn't due to the integer key.

> This may be a philosophical difference,

It is a fundamental, if you ever want more than PFAF data or to do analysis.

> it's better to use a code or description that will identify
> each entity in a more transparent way.

A CPU uses integers, not strings.  Such a subtle difference shows up in OLAP tools and large data sets.  As PFAF stands, it could just as easily be a wiki.  If you want to analyze potential guild associations, the whole architecture changes and these details become very important.  I'm not needed here, if you only do a plant lookup and data entry tool.

> If you want functionality that exceeds what a relational
> database can provide, then the development process
> really becomes a challenge.

A challenge it will be.  Data entry requires normalization.  Analysis requires de-normalization.  It's important to identify how the data will be used.  What you presented gets the ball rolling, which I appreciate.  At the same time, I have interest in OLAP, so I strongly want some of the details you have condemned.  OLAP is my profession (financial markets - wall street - right now; munis specifically).

> 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. 

Let's start with the data entry, but be mindful that some of us want to use the data to do Guild mining.  We will need an artificial key to map the many identifiers in various data sets; PFAF, ITIS, etc.  Since scientific names are a slowly changing dimension, I think these artificial keys are a better solution than to key to scientific name varchars (huge performance implications and volatile).

Feel free to dig up my star schema discussions from the past.  I'm just a DBA, so maybe I don't know any better.


-Sean.

---

Sucker-punch spam with award-winning protection.
 Try the free Yahoo! Mail Beta.