Summary of feedback on database, round 1

[Bruce Raup]

Martin,

Thank you very much for your feedback.  I'm copying my response to the
list in case others had similar questions.

On 2001-02-04 21:58 +0100,  Martin Hoelzle wrote:

> Hi Bruce
>
> Sorry for my late answer for your database desin. First
> I think you have done a great job. I am not a specialist
> in database design but I have some minor questions and comments.
> May I have not understand everything right, so then you can directly
> delete my questions.

Not at all.  These are good points!

> 1. I do not understand why you are creating the table Glacier_Reference,
> if you would take the attribute 'reference_docid' directly in
> Glacier_static you probaly don't need the table Glacier_Reference.

This points out a need for us to communicate the design in a better way.

The Glacier_Reference table is what is called an "intersection table", and
is just a device to implement a "many-to-many" relationship in the
database.

Rules of thumb for database design:

If you have a one-to-many relationship, then records in the "many" table
point back to the "one" table.  For example, one imaging Instrument can
have many band (but not the other way around).  So in the bands table, you
would have a record (or row) for each band, and one field in this record
would be the instrument ID.  To find all the bands for a particular
instrument, just find all the records in the Bands table with that
particular instrument ID.

If you have a "many-to-many" relationship in a database, then an
intersection table is formed, that holds pairs of IDs from the two primary
tables that are related to each other.  For example, a glacier can have
many literature references pertaining to it, and a literature article can
pertain to many glaciers.  Thus, in the intersection table, you can look
up all the refs that are about a given glacier, or all the glaciers in a
particular ref.

> 2. To increase the convenience of the database. I would have expected
> the date of glacier image in the Glacier_Dynamic table. Because the
> date of an image is one of the most important information. If I would like
> for example the date of a certain outline of a glacier, I had to
> select over 4 tables and this seems me quite inconvenient, but I think
> there is no possibility to change that.

We decided to allow for the possibility that an analysis might be based on
more than one image (e.g. might be a mosaic of images spaced a few days
apart).  Thus, between Glacier_Dynamic and Image, we have an intersection
table to implement the many-to-many relationship.

You are right that to find the date of acquisition of the image that an
analysis is based on, the following steps would have to be taken:  1. look
up analysisID in the Glacier_Image_Info table and find all the imageIDs
associated with that analysis; 2. find these imageIDs in the Image
database, and look up the acq_time field for the date.

This would indeed be inconvenient if you were doing it my hand, but the
interface to the database (yet to be implemented) will hide that
complexity from the user.

> 3. You have an attribute speed in the Glacier_Dynamic table, but no
> information about ELA and AAR. Speed, we can directly calculate from the
> table Vector, so I don't see the reason to store this information in
> Glacier_Dynamic. ELA seems to me much more important.

Very good point.  I agree that we should add a scalar ELA field to
Glacier_Dynamic.  Perhaps AAR should be added too, although it would make
sense for some glaciers.  Comments on this, anyone?

By the way, the field for representative speed was added to enable easy
searches based on this parameter.

> 4. A remark: WGMS has two databases: One is the World Glacier Inventory
> and the other is the fluctuations of Glaciers. Both are possible easy to
> link to the new database. Maybe you can add one more key for that in the
> Glacier_Static table.

Good point.

> 5. Concerning the attribute 'glacier_type' in Glacier_static, we may
> should really discuss what we should store there. this attribute contains
> a lot of interpretation and if the people filling out that  are not so
> familiar with
> glaciology a lot of trash is suddenly stored in that attribute. May we
> should define clear standards.

Yes, this is one of several "enumerated data types", or fields where a
value (or values) should be chosen from a fixed list, that we've put in
the database.  These values have not be set yet, so now is a good time to
submit your opinions.  Other fields that need the same discussion are
seg_type, seg_label, seg_matl_left and seg_matl_right in the Segment
table.

> Many Greetings from a very mild Switzerland (today 15 degree Celsisus)
> Martin

It's been mild in Boulder too.  Today's high was 9 degrees C.  I see
Flagstaff was 14, and it was 15 in Kathmandu.  We'd better hurry up with
this project!  :-)

Bruce

-- 
Bruce Raup
National Snow and Ice Data Center                     Phone:  303-492-8814
University of Colorado, 449 UCB                       Fax:    303-492-2468
Boulder, CO  80309-0449                            Bruce.Raup at colorado.edu