Possible changes to Climsoft 4 data model

[isedwards]

Suggestions

1. We have suggested to have a new table(or use the existing qctype) which would contain QcStatus of the records within the observationinitial. These status would be things like QcProcedures, Corrected from Paper etc.

2. We also suggest to have a new field in observationinitial named Status- This would contain numbers indicating different entries of a record say 1,2,3....which would be used to check the best current value as well as what the previous values were. Its also needed because QcStatus does not tell indicate what the current best value is.

3. We also suggest a new field named Comment- which would have a restricted reasonable length which would indicate the reason for the new value.

Questions/Concerns

1. We would like to know what happens to a record for the purposes of differentiating the records when upload happens from the forms. We think one of the QcStatus would change to indicate this transition.

2. We are need to decide what the key columns would be. Currently we suggest recordedfrom, describedby, obsdatetime, state, acquisitiontype?( we are not sure what this is from the current table), Source?(We suggest to have this as a new column which would come from a new table which would enable us know how to repopulate the data entry forms from ObservationInitial)

3. We would also like to understand better how all this relate to obsaervationFinal table .From earlier discussions we understand that it was desirable to have it remain as data would be transferred there from time to time.

[isedwards]

2. We also suggest to have a new field in observationinitial named Status- This would contain numbers indicating different entries of a record say 1,2,3....which would be used to check the best current value as well as what the previous values were. Its also needed because QcStatus does not tell indicate what the current best value is.

Currently, we know which observation is the "current best value" because it is stored in observationfinal instead of observationinitial.

@maxwellfundi - If we know which record is the current best value, is there any reason why we need to record the order in which the other previous versions of the observation were recorded?

[dannyparsons]

@isedwards I thought that the best version is not necessarily always in the observationfinal table, only when it has gone through sufficient quality control? For example, after data entry or double data entry or some initial quality control, there might only be values in the observationinitial table. In that case, shouldn't we still have a current best value in the observationinitial table? Or is this not how it would work?

[isedwards]

Yes, you're right. That is how it currently works.

[dannyparsons]

Do we continue with this or propose something different?

[isedwards]

We should continue to use observationinitial and observationfinal as they are currently being used. In the countries where Climsoft 4 is already installed the databases will have existing data in these two tables.

[dannyparsons]

Ok. Does this mean that adding any new fields to these tables would cause problems for existing users?

[isedwards]

We may be able to add fields without causing any problems.

The new code will need to be able to cope with observations that do not have data in the new fields. When the new field is first created, existing rows will either be null, or they can have a default that we specify.

We should keep in mind that an organisation could have other machines that still connect to the database using instances of v4.1.x as well as our new 4.2 (even if this is only for a couple of days, but it's easy to imagine that it could be longer). So we need to understand how this would create inconsistencies and be able to handle legacy observation when we encounter them.

We can't detect and fix all inconsistencies on each start up because it would be too slow.

If 4.1.x and 4.2 cannot coexist then we'll have to consider moving to version 5. Alternatively we'll have to work with the places that we know have installations to make sure everything is updated simultaneously (but that would be bad practice because other people may be using the software unsupported).