Skip to Content.
Sympa Menu

pcplantdb - Re: [pcplantdb] schema stuff

pcplantdb@lists.ibiblio.org

Subject: pcplantdb

List archive

Chronological Thread  
  • From: John Schinnerer <john@eco-living.net>
  • To: Permaculture Plant Database <pcplantdb@lists.ibiblio.org>
  • Subject: Re: [pcplantdb] schema stuff
  • Date: Tue, 16 Nov 2004 23:48:25 +0000

Aloha,

I'll be at 30K feet and above on all this - conceptual not technical since it's not my kuleana...bits I don't comment on means either I agree with Chad's proposal or have no freakin' clue what he's talking about... ;-)

Some norms I'd like to introduce:

o Table naming: For increased clarity I'd like to start every table
with the first one or two words being the type of objects
contained/related. Eample: plant_name, relationship_types, and
for indexs plant_relationship_index, etc.

I completely support clear table names...this sounds clear to me.
No cryptic abbreviations please, the era of 8.3 is long gone...

o XMLized dataset: The one place where I think XML actually does
make a lot sense is inside the database itself. Three tags I can
think of immediate are temperature, height, and link tags.
Clients in the US will want Fahrenheit and Feet while Europeans
will want Celsius and Meters. Non html based clients will need to
be able to translate links into something meaningful.

See previous reply - I agree with RichardA on this, no XML in dataset, it's for data transport only.

> Database: eden Table: culture Rows: 7396 Wildcard: %
+---------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | |
select |
| hardyness | tinyint(4) | | | 11 | |
select |
| range | tinytext | | | | |
select |
| habitat | tinytext | | | | |
select |
<snip>

I think 'culture' is wrong name for this sort of stuff.
I would like 'culture' to be keyed to ethnobotanical information.
Not sure what to call this grouping...there's quite a mix of info in there...functional stuff, performance stuff, inputs/outputs stuff...

This is the table that serves as the basis for the paragraph long
summaries produced for each plant. Either this needs to change from a
one to one table to a many to one or the localized info needs to be
split out to a new table.

> Database: eden Table: plant_locations Rows: 7384 Wildcard: %
+-----------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-----------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | |
select |
| woodland_garden | char(1) | | | N | |
select |
| canopy | char(1) | | | N | |
select |
| secondary | char(1) | | | N | |
select |
| sunny_edge | char(1) | | | N | |
select |
| dappled_shade | char(1) | | | N | |
select |
| shady_edge | char(1) | | | N | |
select |
| deep_shade | char(1) | | | N | |
select |
| other_habitats | char(1) | | | N | |
select |
| cultivated_beds | char(1) | | | N | |
select |
| ground_cover | char(1) | | | N | |
select |
| lawn | char(1) | | | N | |
select |
| meadow | char(1) | | | N | |
select |
| hedge | char(1) | | | N | |
select |
| hedgerow | char(1) | | | N | |
select |
| pond | char(1) | | | N | |
select |
| bog_garden | char(1) | | | N | |
select |
| walls | char(1) | | | N | |
select |
+-----------------+----------------------+------+-----+---------+-------+------------+
This is another table that comprises part of the summary data, but
needs a bit of refocusing I think. I can think of way more possible
categories but listing them all and evaluating each plant for
qualification seems like overkill.

This looks like functional and relational information - very PC stuff, no?

Perhaps this kind of information
can be captured by relationship information.

Don't follow - how would the relationship information that would capture this be stored?

Database: eden Table: cultural_notes Rows: 7396 Wildcard: %
+---------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | |
select |
| cultivation_details | text | | MUL | | |
select |
| propagation_details | text | | MUL | | |
select |
| known_hazards | tinytext | | MUL | | |
select |
+---------------------+----------------------+------+-----+---------+-------+------------+

Should also be known_uses...and...??
Actually if this is to be ethnobotanical info a lot more thought is needed.
For some reason just a few text fields seems insufficient - depending on how they would be editable.
cultivation_details and propagation_details may have multiple stories each, depending on the culture doing the cultivating and/or propagating.
Maybe other factors too such as regional conditions that change the necessary cultivation and/or propagation info.
Throwing it all into one text field, hmm, I don't really know but it seems like that would be limiting and/or messy and/or hard to search effectively...?

XML here I still don't like.
A good data model will work better than just XMLizing textual info, I think.


Database: eden Table: cultivars Rows: 1902 Wildcard: %
+-------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | |
select |
| cultivar | tinytext | | | | |
select |
| notes_on_cultivar | text | YES | MUL | | |
select |
| synonyms | tinytext | YES | | | |
select |
+-------------------+----------------------+------+-----+---------+-------+------------+
Although there aren't many listed yet (1902) I think users will
probably submit a lot of cultivar info. I'm not sure if this should
be handled as comments (the cleaner method) or continue with a
plant_cultivars table. Waddya think?

How would comments be cleaner? You mean for implementation, for user, both?
How would they be stored and searched?

Database: eden Table: references_list Rows: 270 Wildcard: %
+------------------+-------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+------------------+-------------+------+-----+---------+-------+------------+
| number | smallint(6) | | PRI | 0 | | select |
| title | tinytext | | | | | select |
| author | varchar(50) | YES | | | | select |
| comments | tinytext | | | | | select |
| publisher | varchar(50) | YES | | | | select |
| publication_date | smallint(6) | YES | | | | select |
| isbn | varchar(50) | YES | | | | select |
+------------------+-------------+------+-----+---------+-------+------------+
The 270 books that Ken Fern pawed through to create the dataset. In
the past I have been in favor of dropping this table because although
the dataset was created largely by referencing books I seriously doubt
it will progress that way. Most of our data from now on I expect to
get from users sharing personal experience who will not reference
books.

I think we should continue to be able to handle references to outside sources. Maybe a bit more flexible than this, to allow for A/V (multimedia) sources and virtual sources as well as books and periodicals.

Database: eden Table: rating Rows: 7396 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | | select |
| pfaf | tinyint(3) unsigned | | | 1 | | select |
+-------+----------------------+------+-----+---------+-------+------------+
I really liked this concept early on and pcpdb (pre-eden search
engine) sorted search results by order of rating. Not sure how this
figures into the whole scheme. Perhaps this is a table to be dropped
as we will probably have a more advanced scheme for qualifying
information.

Yeah, I don't see how this particular setup would work with the rating and/or peer review systems we've discussed.

On the scent table sort of stuff - some of that might be particular forms of classification that are pretty different from each other.
There's the scientific names for types (classes) of scents, and then there's the aromatherapy sort of info about effects of various plants' scents, then there's other functional scent-related stuff (insectary scents, pollinator-attracting scents, etc.)
I think the functional ones would fit better with other functional information (climate, inputs, outputs, shade/sun, etc.)


o Images: This should be very straightforward.

I reckon there will get to be some complexity here if we actually start getting a variety of images for some plants.
Images will need to be classified somehow to be most useful - seed, seedling, sapling, mature, close-ups and detail shots of various kinds/parts and so on.

Hasta luego...Zzzzzzz...


--

John Schinnerer - MA, Whole Systems Design
------------------------------------------
- Eco-Living -
Whole Systems Design Services
People - Place - Learning - Integration
john@eco-living.net
http://eco-living.net




Archive powered by MHonArc 2.6.24.

Top of Page