In reviewing the SQL schemas for version 3, it came to my attention that we have an issue with the structure of the table in terms of keys, because pipelineID is set as a primary key, but within the calculations table is repeats for various treatmentID value within the table.
In an attempt to solve this as quickly and with as minimal interruption as possible, I have suggested that we treat the calculationsID as a PK, and the pipelineID as an FK of that, which is then referenced also as an FK within in the measures table. I’ll need to draft some amendments to the documentation on how to use the calculations table, that include populating a mostly blank row (ie. no treatmentID) for each pipelineID. This was the CK of the calculationID will just be the pipelineID value, and the structure should hold.
I’ll also add a new calcType value of pipelineHeader to distinguish these rows, I think.
So we go from something like this:
To something like this:
[grid]
It’s a bit of a “hack job” but it seems the cleanest solution. I’ll start on drafting for it, but would welcome any additional commentary from @dmanuel or @jeandavidt