pcplantdb

[John Schinnerer <john@eco-living.net>]

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