Bug #786


Update the Plots datamodel to allow for individual trees

Added by Michael Lee over 21 years ago. Updated about 19 years ago.

Target version:
Start date:
Due date:
% Done:


Estimated time:


Discussion with Boyle & Enquist led to realization that we can improve the data
model for individual tree stems to better meet the needs of the stem-map
community. This can wait until early 2003. However, in as much as trees are not
being loaded now, would make sense to do this before we really load trees.
Probable solution is separate table for stems attached to TaxonObservation, but
for performance may need separate table. -- DECIDE THIS ISSUE!

Files (259 KB) Michael Lee, 01/15/2003 10:15 AM

Related issues

Blocked by VegBank - Bug #788: Load tree-related dataResolvedGabriel Farrell11/13/2002

Blocked by VegBank - Bug #1169: Upgrade VegBranch to new 1.0.2 Vegbank data modelResolvedMichael Lee10/02/2003

Blocked by VegBank - Bug #870: New XML export and import into the VegBank systemResolvedGabriel Farrell11/13/2002

Actions #1

Updated by Michael Lee over 21 years ago

The current solution will be for us to add one field to TaxonObservation

taxonObsType varchar(30) [nulls are OK]

acceptable values are (currently):

"collective" -- taxonObs applies to a collection of individuals
"individual" -- taxonObs applies to an individual

"collective" should be the default value unless 'individual' is specified

Actions #2

Updated by John Harris about 21 years ago

An example dataset would be good.

Actions #3

Updated by Michael Lee about 21 years ago

This db is scarcely populated. Data that concern us are in TaxonObs,
StemCount, and StemLocation. To download, you'll get download.cgi or
something, then you'll need to change the extension to .zip and then extract
the db therein.

Actions #4

Updated by Robert Peet about 21 years ago

Various users need individual tree attributes to be contained
within the database. They cite the case of each tree having a voucher
museum collection (or even multiple vouchers).

Because of the potential existence of multiple vouchers, we agreed to add
a table for vouchers.

The essential problem with the current data model is lack of taxonomic
determination of individual trees. Two primary solutions were discussed.

(1) Create a table parallel to Taxon_Observation for individuals,
called perhaps Individual_Observation. The problem here is that we would
need a second Individual_Interpretation table, or we would need to place a
switch in Taxon_Interpretation so that a record could point to a record in
either of the two tables. Similarly, the Voucher table would need a switch
so that a record could apply to either the Taxon_Observation or

(2) Add an Observation_Type to Taxon_Observation which indicates
whether this is an individual stem or a collective record. If a
collective observation, then current fields like cover could be populated,
and if an individual observation then a record could be placed in a linked
stems table. This solution has a performance price in that there will be
millions of added entries in the table to handle that many individual

We recognized that solution #2 is the best for use in a conceptual data
model, while recognizing that the actual physical model implemented may
need to be closer to #1 for performance reasons.

I think we agreed to start with solution #2, but we could revisit this. I
am anything but sure about it being the better choice.

Here is the basic data model (replacing TaxonObs, StemCount and
StemLocation for solution #2.

TABLE: TaxonObservation)  ---- A slight revision of hte table
TaxonObservation_ID = PK
Observation_ID = FK to the plot
BiologicalType (Collective or Individual)
TABLE: Stem (replaces current stemCount and stemLocation tables)
Stem_ID = PK
TaxonObservation_ID = FK
Stem_ID = Recursive FK for repeated observations
(Each set would need a new Observation entry and thus
A new Taxon_Observation_ID record)
TABLE: Voucher
Voucher_ID = PK
TaxonObservation_ID = FK
Party_ID = collector

As soon as we agree that we want solution 2 rather than 1, you could build
this into VegBank and then populate it with some example data.

Actions #5

Updated by Michael Lee over 20 years ago

3 options, as outlined
id=86&action=view are, to leave things the same and use business rules to deal
with this issue. Option B is to add one field to differentiate between
records that are dealing with all of a taxon on a plot, and those records that
only deal with part of the whole taxon. Option C is to make all the changes
requested by Bob. I lean towards A or B, with a slight preference for B so
long as adding the field is trivial for Gabe. I think C is unnecessary right

Vouchers can be dealt with separately, since they would be completely new to
the system. We could also use vouchers in a way similar to notes. Someone
could then specify tableName and fieldName and PK# to add a voucher for
anything in the database: plant, soil, etc.

Actions #6

Updated by Robert Peet over 20 years ago

Are not tree stems largely new to the system in that we have never tried to
load any in Vegbank, and I rather doubt any code has been written for them
aside from the table construction.

I vote to do this once and do it correctly. Am open to discussion as to when
we do it.

I remain very open minded as to whether stems should be a child of
taxonobservation or of a new table called individualObservation. I am
currently leaning more toward having the parallel individualObservation. If
we go this way we need to decide whether taxonInterpretation applies to both
individualObservation and taxonObservation or if we need a new
individualInterpretation. Makes no difference to me. The first is perhaps
more elegant, but there would be less to reengineer if we constructed a new
parallel stemInterpretation.

Actions #7

Updated by Michael Lee over 20 years ago

Given Bob's comment of "Let's do this once and do it right" it seems to me best
to leave the model as it is for now and implement any additional changes in
functionality by adding values to closed lists and/or business rules. This
keeps Gabe happy, too, as model changes are cumbersome to implement with a
populated database.

Actions #8

Updated by Michael Lee over 20 years ago

A clarification of my last comment:

Revising the structure of a populated database is somewhat difficult, even if
the tables being changed are not the ones populated. We are attempting to
ensure that everything flows from one source, an XML document describing our
database. So the process to change the db structure would be to change that
document, drop the current database, then create the new one. Obviously, before
we drop the intitial database, we have to backup the data and then restore the
data to the new database, directly where possible. Where changes have been made
to the structure, this becomes more difficult, as the backed up data may have to
be fiddled with to get it back into the new database.

[the alternative would be to "hack" the new tables in manually at a SQL prompt,
but this introduces the possibility that the implemented database aspects (java,
sql, model, vegbranch) could disagree if there was a typo in the manually
entered SQL. This possibility is greatly reduced if all things are generated
from the same source.]

But the general idea with datamodel changes should be to add functionality not
subtract it. So the "old data" may have no information about the new fields we
might want to add, but they should stay completely intact. I think this is true
for our stems model change, as all fields are kept, just the tables are being
combined into only one table and maybe a few new fields will be added.

Furthermore, another reason to wait is that Gabe has no time left. There are
more important things for him to deal with now and this is one of the several
things that will need to be left for the next person. Especially since we don't
seem to be convinced of how to deal with the taxonInterpretation w.r.t. stems
specifically (ie 2 tables: individualInterpretation and taxonInterpretation or
what?). This certainly needs resolving if we are to "fix it once and fix it right"

Actions #9

Updated by Michael Lee over 20 years ago

we should fix this as we are fixing the irregular taxa model change, which must
be done by Nov. 1. This one requires adding fields to taxonInterpretation
only. Must be mirrored in VegBranch, whish is bug 1169

Actions #10

Updated by Michael Lee over 20 years ago

This change has been made and no java code should sit on this, save perhaps only
importing via XML. At current time, we can't make a taxonInterpretation of an
extant taxonObs. When this is made, we will have to allow for this to be
attached to either TaxonObs or StemLocation.

Actions #11

Updated by Michael Lee about 19 years ago

changed from components that are to be deleted to "misc" so that bugs don't get
deleted with component. Sorry for all the email.

Actions #12

Updated by Redmine Admin almost 11 years ago

Original Bugzilla ID was 786


Also available in: Atom PDF