Schema - Remove 'comments' from layer_data table and make own measurement type

[jomey]

The comments column in the layer_data column has a lot of null values after uploading one stratigraphy.csv. Example screenshot here:

Additionally, with the new structure of having one row per measurement type, we really can't determine to which of those measurements per layer a comment belongs to.

Suggestion:

Drop the column and make the comment a new measurement type that gets logged with each layer that has one. This will make it clearer, when something is a comment and does not produce duplicate values for "value" and "comment". It also makes a consistent treatment of one column being a measurement from the pit sheet.

Let's discuss @micah-prime @micahjohnson150 @aaarendt

[micah-prime]

@jomey my understanding is the comments come from a couple places on the pit sheet. The "general" pit sheet comments and then the comments about a specific layer, which may only be in the stratigraphy file.
I could see merit in one being a site level comment and one should be a "measurement".
My big concern is changing schema at this point would be pushing timelines out.

[micahjohnson150]

There are two places they show up. Comments on the top of the pit sheet, and comments on the individual layer.

I think something beside the brute force method I used sounds great. I am not sure I totally follow the implementation but IMO as long as the layer with comments at that depth is reference-able it will be an improvement. The graupel example I used in hackweek would rely on this functionality and IMO shows the power of the DB.

I do share some of primes reservations on pushing timelines out but you will have this living on at BSU so you will inherit whatever tech debt we do or dont generate here.

[jomey]

I agree that this a bigger change and also manageable.

Thanks for sharing the graupel example. Looking at that, we anyways have to revisit that and join all the layer records based on top and bottom depth. So from that perspective, this change won't make it any more complicated.

Right now we are adding the pit comments (top right) onto the layer, which seems an odd place to put it. Personally, I would not look for that info on layers, but rather on the site. Think we need a comment column there, instead of layers.

Let me quickly prototype this out from a schema perspective and open an PR. Maybe this idea will be easier to follow then.

[micahjohnson150]

hahaha that's funny, I feel the opposite which is why they ended up there. Sounds good to me. I will keep an eye out for the PR. Thanks Joe!

[jomey]

Turns out this is so far an easy change. We actually already did most of the suggested logic in snowex_db anywasy (See SnowEx/snowex_db#63) and dropping the column seems natural.

Still have a few issues on the logic around comments that I found in insitupy
M3Works/insitupy#26
M3Works/insitupy#25

To be continued ...

[jomey]

Here how the layer and measurement type table look with the suggested change: