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@colorado.edu