Proposed ODM2.1 schemas for discussion

[aufdenkampe]

I'm creating this issue as a central place to track progress and discussions on our various proposed changes to ODM2 schemas into what might eventually become ODM2.1. These proposed changes are being saved to this new branch: https://github.com/ODM2/ODM2/tree/ODM2.1_dev

Please note that a full release of ODM2.1 is still a long way off, as we have a large and diverse ODM2 software ecosystem that depends on the current ODM2.0 schema.

Background & Motivation

We've been accumulating ideas for a revision to ODM2.0 since we first "froze" the schemas in April 2014. In recent years many of these ideas have been posted as GitHub issues, which we had often tagged with the ODM2.1? label.

Now that Roelof Versteeg (Subsurface Insights) has acquired DOE SBIR Phase 1 funding to prototype a full integration of ODM2 with his Predictive Assimilation Framework (PAF) web application, we have a motivation (and I have a little funding) to draft and test ODM2.1 over the next 5 months. If successful, we hope to receive DOE SBIR Phase 2 funding to fully develop an ODM2.1 software ecosystem over the next several years.

Plan

I'm hoping to post diagrams under this issue for all proposed schema changes implemented in the ODM2.1_dev branch based on ODM2.1?-tagged issues. My hope is that posting all diagrams in one central place will help lessen confusion from cumulative modifications.

In the meanwhile, I'm hoping that the discussions of these changes will continue to take place within each specific issue thread, as we have been doing.

cc: @horsburgh, @emiliom, @miguelcleon, @dr-shorthair, @smrgeoinfo

[aufdenkampe]

Issue #130:

• Modify FeatureActions to track who, how, when for Actions that create SamplingFeatures #130

Led to commit 016bb2d on Nov. 1, 2016 and 3 subsequent commits, which collectively resulted in these changes:

• To ODM2Core.FeatureActions table, added RelatedFeaturesRelationID field, which is a foreign key to ODM2SamplingFeatures.RelatedFeatures.

Proposed for ODM2.1:

Existing in ODM2.0:

[aufdenkampe]

Issue #152:

• Remove Sites table, moving Lat/Lon to SamplingFeatures #152

Spun off from ideas in:

• Resolve inconsistencies between SamplingFeatureTypeCV & SiteTypeCV #142 (comment) and below,

which led to commit 019bc37 on Aug 8, 2018.

Proposed for ODM2.1:

Existing in ODM2.0:

• See the immediate comment above, at Proposed ODM2.1 schemas for discussion #153 (comment).

[aufdenkampe]

Issue #132:

• Feature of Interest #132

Led to commit a7e2738, on Aug 8, 2018, which:

• Added a simple FeaturesOfInterest table;
• Created a new FeaturesOfInterestTypeCV, based off the old SiteTypeCV,
  • which in turn was based off USGS Site Types as discussed in Resolve inconsistencies between SamplingFeatureTypeCV & SiteTypeCV #142;
• Coupled to the SamplingFeatures table via a many-to-many bridging table that leverages the existing RelationshipTypeCV that was created for the ODM2Provenance schema.

Proposed for ODM2.1:

[aufdenkampe]

Issue #135:

• Actions should have a name  #135

Led to commit f2bda09 (Added ActionName), resulting in this diagram:

[aufdenkampe]

Here's the Overview diagram of the current draft ODM2.1 schema.

[aufdenkampe]

cc @roelofversteeg, @erekalper, @dougvj

[aufdenkampe]

Issue #155:

• Storing the minimum and maximum value for a range #155

Led me to commit 6014915 (Modified MeasurementResults), which:

• Created 1-to-Many relationship between MeasurementResults and MeasurementResultValues, to allow storage of values for different AgregationStatistics (such as both mean and standard deviation).
• Moved AggregationStatisticCV, CensorCodeCV and QualityCodeCV to Values table, so that these are clearly connected to the corresponding value.

Here's what Results looks like now:

[aufdenkampe]

Issue #160 and others:

• Modify Variables table to enable greater interoperability #160
• Change UUID field from PostgreSQL OIDs, which are now obsolete #157
• Move NoDataValue from Variables to Results #158
• Move EquipmentType FK from Equipment table to EquipmentModels table #159
• Eliminate use of Speciation CV b/c redundant with entries in TaxonomicClassifier. #161

Led @roelofversteeg and me to these commits: 4c718c3, 8ad71c4, and 0698990.

The major changes are to the Variables and TaxonomicClassifiers tables, as described in #160 (comment) and shown below.

For details, please these commit notes, including comments at the bottom of each of these commits: 4c718c3, 8ad71c4, and 0698990.