Skip to Content.
Sympa Menu

pcplantdb - [pcplantdb] schema stuff

pcplantdb@lists.ibiblio.org

Subject: pcplantdb

List archive

Chronological Thread  
  • From: Chad Knepp <pyg@galatea.org>
  • To: pcplantdb@lists.ibiblio.org
  • Subject: [pcplantdb] schema stuff
  • Date: Tue, 16 Nov 2004 12:09:26 -0600

Ok here is some schema stuff. You'll need to have a fixed width font
and at least 100 character wide display to see this correctly. If the
tables don't line up you probably have a proportional font.

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.

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.

o Although I would like to have a master index of integer ids
referencing everything in the dataset I haven't figured out a
fully normalized implementation. Possibly forthcoming or
otherwise a naming scheme will be implemented with elements like
plant1537 and comment683 ([a-z])([0-9]).

Some comments on the existing schema and proposed changes:

Database: eden Table: botanical_name Rows: 7396 Wildcard: %
+------------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default |
Extra | Privileges |
+------------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 |
| select |
| legacy_pfaf_latin_name | varchar(100) | | | |
| select |
| family | varchar(50) | | MUL | |
| select |
| genus | varchar(50) | | | |
| select |
| species | varchar(50) | | | |
| select |
| ssp | varchar(50) | YES | | |
| select |
| common_name | varchar(50) | YES | MUL | |
| select |
+------------------------+----------------------+------+-----+---------+-------+------------+
This should change to plant_name or plant_botanical_name. One of the
significant ways the eden dataset difference from the pfaf one is that
the primary key for most tables in pfaf is a 2 - 3 word Genus species
ssp/var/forma combo. Eden uses an integer id and separates the latin
name into it's component parts. It's not clear to me what exactly the
third word of the name represents (either ssp, variety, forma,
whatever) and I don't think the pfaf dataset is clear about it either.
Not sure where to go with this. legacy_pfaf_latin_name is retained in
order to syncronize with later pfaf datasets. I didn't think it was
important at the time but [latin name] author should probably go back
in here. common_name should go in it's own [many to many] table with
locale support.

Database: eden Table: synonyms Rows: 4579 Wildcard: %
+------------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default |
Extra | Privileges |
+------------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 |
| select |
| legacy_pfaf_synonym | varchar(100) | YES | | |
| select |
| author | tinytext | YES | | |
| select |
| legacy_pfaf_latin_name | varchar(100) | YES | | |
| select |
| genus | varchar(50) | YES | | |
| select |
| species | varchar(50) | YES | | |
| select |
| ssp | varchar(50) | YES | | |
| select |
+------------------------+----------------------+------+-----+---------+-------+------------+
This is a many to one table that should be renamed plant_name_synonyms
or some such. Interestingly enough author remains... Probably can
drop legacy_pfaf_latin_name in this table.

Database: eden Table: hybrids Rows: 98 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | | select |
+-------+----------------------+------+-----+---------+-------+------------+
Kind of related to plant names is hybrids. Some of the plants are
hybrids and it is not indicated elsewhere. Eden currently does not
check/display this. This should maybe go back into the plant_names
and plant_names_synonyms tables, or into the plant_culture below.

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 |
| habit | varchar(50) | | | | |
select |
| deciduous_evergreen | char(1) | | | D | |
select |
| height_centimeters | smallint(5) unsigned | | | 100 | |
select |
| width_centimeters | smallint(5) unsigned | | | 50 | |
select |
| growth_rate | char(1) | | | M | |
select |
| nitrogen_fixer | char(1) | | | N | |
select |
| supports_wildlife | char(1) | | | N | |
select |
| months_in_leaf | varchar(50) | YES | | | |
select |
| months_in_flower | varchar(50) | YES | | | |
select |
| flower_type | char(1) | | | H | |
select |
| pollinators | varchar(128) | YES | | | |
select |
| self_fertile | char(1) | | | N | |
select |
| months_seed_ripe | varchar(50) | YES | | | |
select |
| scented | char(1) | | | N | |
select |
| soil | char(3) | | | LMH | |
select |
| heavy_clay | char(1) | | | N | |
select |
| poor_soil | char(1) | | | N | |
select |
| ph | char(3) | | | ANB | |
select |
| acid | char(1) | | | N | |
select |
| alkaline | char(1) | | | N | |
select |
| saline | char(1) | | | N | |
select |
| moisture | char(3) | | | M | |
select |
| drought | char(1) | | | N | |
select |
| shade | char(3) | | | SN | |
select |
| wind | char(1) | | | N | |
select |
| pollution | char(1) | | | N | |
select |
| frost_tender | char(1) | | | Y | |
select |
+---------------------+----------------------+------+-----+---------+-------+------------+
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. As I suggested I would like to change
height_centimeters and width_centimeters to height and width
containing something like <distance type=centimeters>1200</distance>.
As we discussed earlier there are many additions and changes that
apply to this table that I won't reiterate here.

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. Perhaps this kind of information
can be captured by relationship information.

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 |
+---------------------+----------------------+------+-----+---------+-------+------------+
Database: eden Table: uses_text Rows: 7396 Wildcard: %
+-----------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-----------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | | select
|
| edible | text | YES | MUL | | | select
|
| medicinal | text | YES | | | | select
|
| other | text | YES | | | | select
|
+-----------+----------------------+------+-----+---------+-------+------------+
These two tables are paragraph sized comments about the plants. I
suggest that they be incorporated into a comments table. Since we
need to have comments be able to be attached to anything including
plants, relationships, and other comments this needs to be carefully
thought out. The content of these comments also really need
XMLization as they are primarily written by Ken Fern and are full of
reference like [49, 67] where the numbers refer to ids in the
references table but have no usable linkage. These should be changed
to <link> tags. At one point I wrote a script that parsed every
comment of every plant for every reference of this sort and generated
what is now called references_index. After the XMLization we could
drop this table.

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?

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. See also references_index and uses_text/cultural_notes.

Database: eden Table: uses_index Rows: 34006 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | MUL | 0 | | select |
| uses | varchar(100) | | MUL | | | select |
+-------+----------------------+------+-----+---------+-------+------------+
This is a many to many table with plant ids corresponding to what they
are used for. I would like to have a better way to do this.

Database: eden Table: uses_glossary Rows: 271 Wildcard: %
+------------+--------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+------------+--------------+------+-----+---------+-------+------------+
| uses | varchar(100) | | MUL | | | select |
| definition | tinytext | | | | | select |
+------------+--------------+------+-----+---------+-------+------------+
Rather pedantic definitions of the various uses. I would like to drop
this table.

Database: eden Table: cache Rows: 102 Wildcard: %
+---------+---------------+------+-----+---------+-------+----------------------+
| Field | Type | Null | Key | Default | Extra | Privileges
|
+---------+---------------+------+-----+---------+-------+----------------------+
| search | varchar(255) | | PRI | | |
select,insert,update |
| time | timestamp(14) | YES | | | |
select,insert,update |
| counter | int(11) | | | 1 | |
select,insert,update |
| page | text | | | | |
select,insert,update |
+---------+---------------+------+-----+---------+-------+----------------------+
This table is complete internal and a performance enhancing aspect of
eden as opposed to being part of the dataset proper. Just ignore this.

Here are some legacy pfaf tables that are currently unused by eden
that I recommend dropping until/unless we need them. Don't worry
about information loss, because we can always import it again from an
archive of the eden or pfaf dataset at a later date if desired. I'm
not sure that even pfaf uses these tables.

Database: eden Table: composition Rows: 146 Wildcard: %
+---------------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | |
select |
| part_of_plant | varchar(50) | | | Fruit | |
select |
| dry_or_fresh_weight | char(1) | | | F | |
select |
| water | float | YES | | | |
select |
| calories | float | YES | | | |
select |
| protein | float | YES | | | |
select |
| fat | float | YES | | | |
select |
| carbohydrate | float | YES | | | |
select |
| fibre | float | YES | | | |
select |
| ash | float | YES | | | |
select |
| calcium | float | YES | | | |
select |
| phosphorus | float | YES | | | |
select |
| iron | float | YES | | | |
select |
| magnesium | float | YES | | | |
select |
| sodium | float | YES | | | |
select |
| potassium | float | YES | | | |
select |
| zinc | float | YES | | | |
select |
| vitamin_a | float | YES | | | |
select |
| thiamine | float | YES | | | |
select |
| riboflavin | float | YES | | | |
select |
| niacin | float | YES | | | |
select |
| vitamin_c | float | YES | | | |
select |
| vitamin_b6 | float | YES | | | |
select |
| source | tinytext | YES | | | |
select |
| notes | tinytext | YES | | | |
select |
+---------------------+----------------------+------+-----+---------+-------+------------+
This table has nutritional information for 146 plants... too small to
be usefull IMHO.

Database: eden Table: range Rows: 7394 Wildcard: %
+---------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+---------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | PRI | 0 | |
select |
| britain | tinytext | YES | | | |
select |
| europe | tinytext | YES | | | |
select |
| mediterranean | tinytext | YES | | | |
select |
| w_asia | tinytext | YES | | | |
select |
| e_asia | tinytext | YES | | | |
select |
| n_america | tinytext | YES | | | |
select |
| s_america | tinytext | YES | | | |
select |
| africa | tinytext | YES | | | |
select |
| australasia | tinytext | YES | | | |
select |
| other | tinytext | YES | | | |
select |
+---------------+----------------------+------+-----+---------+-------+------------+
This is an attempt at some localization in the form of where the plant
lis native. I say we drop this and do a more focused version of
localization.

Database: eden Table: references_index Rows: 70607 Wildcard: %
+-------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | | select |
| book | smallint(5) unsigned | | | 0 | | select |
+-------+----------------------+------+-----+---------+-------+------------+
A generated index that could be dropped if the dataset was properly
XMLized.

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.

Database: eden Table: scented_plants Rows: 1169 Wildcard: %
+-------------+----------------------+------+-----+---------+-------+------------+
| Field | Type | Null | Key | Default | Extra |
Privileges |
+-------------+----------------------+------+-----+---------+-------+------------+
| id | smallint(5) unsigned | | | 0 | | select
|
| plant_part | tinytext | YES | | | | select
|
| fresh | tinyint(4) | YES | | | | select
|
| crushed | tinyint(4) | YES | | | | select
|
| dried | tinyint(4) | YES | | | | select
|
| scent_class | tinytext | YES | | | | select
|
| notes | tinytext | YES | | | | select
|
+-------------+----------------------+------+-----+---------+-------+------------+
1169 plants and how they smell. If this is important stuff we should
add it to plant_culture, otherwise drop it.

Other major additions include:

o Users and authentication: I think this will be fairly easy and
I'll just store all user/editor info with hashed passwords in a
table.

o Relationships: Still thinking on this some, and somewhat
intimidated by the kind of table magic I suspect will need to
happen but I can at least see a table defining different sorts of
relationships and table(s) that relate different elements with
each other... possibly with some kind of set notation... possibly
in XML. I'll get back to you on this one.

o Comments: As before I really want to see comments (and
relationships) being able to be attached to almost anything in the
dataset. Comments themselves are fairly straight forward in terms
of dataset manipulation.

o Images: This should be very straightforward.

o Other kinds of elements: Farther off on the horizon is the
possibility of adding animals, techniques, articles, etc. I'm not
going to focus on any of these things at the moment, be I am
keeping in my head their possibility and developing so as not to
exclude them.

Enough for now. I will send another email today about a proposed
development path.

--
Chad Knepp
Perl - $Just @when->$you ${thought} s/yn/tax/ &couldn\'t %get $worse;




Archive powered by MHonArc 2.6.24.

Top of Page