Hi,
The value attribute is set as character in the table measure. Why not as numeric? Having value as character is far than simple to handle in term of data management. I can foresee circumstances when character might be needed as for example a positive/negative result but in such a case it can be replaced respectively by 1/0 with unit mentioning it is reported as a positive/negative result. Let’s imagine a scale results from super hard/hard/fairly easy/easy. Again it can be replaced by a scale respectively from 4 to 1. What do you think?
Thanks for your reply.
Hello! Thank you for this point and for this question, and I take your point well. Apologies that it’s taken me so long to respond to it.
The idea for having the value field data type as varchar was too allow as many kinds of data inputs as possible. Ranging for numeric (the most popular) but also categorical inputs as all. To you point on the true or false/1 or 0, and likert-scale like responses, I think that’s also reasonable.
The issue arises for items that record characteristics, usually for sites. Measures like weather, or sample characteristics, which have values like “sunny”, “rainy”, “cloudy”, etc. Technically in the sets table these values now have enumerations, so they could be referenced as numeric if need be. The reason we’ve left it as varchar though is to facilitate ease of input, though I take your point again about the complications it causes for analysis.
I suppose my question back to you is: do you find that the varchar data type is enough of a problem for analysis that you would prefer to use the sets table enumeration key for all character values? And even at the input stage?
Thank you for your feedback and suggestion!
Hello @wastewater_belgium – just wanted to follow up on this to see if you’re able to give a little more context in line with my above comment. Thank you so much!
Hi,
I am really sorry for my very late response.
If there was only one variable that I would set as numeric in the entire db it is this one for data consistency and standardisation reasons. At the beginning of covid we had a character variable to store the results. It led to many issues. For instance in French you can have 5.600. In English it would be 5,600. If you read 5,600 in French it is equal to 5.6 in English. With numeric variability there is no ambiguity.
I would use code lists in case of categorical results. The sets table is already designed for that as you mentioned. For instance with weather, setID=weathSet is the name of the code list. I would add an extra variable in mesasure table where we could indicate the codelist ID (setID) in case the value reported refers to a code list. This new variable would be completed only in case of code list (unit would stays empty). Otherwise it is left blank (and unit is completed). I would also use a specific setType for sets which represent a categorical results.
No worries on the late response - and I see your point and agree! it would certainly help standardize and streamline validation, even if it adds one additional degree of separation for human readability. The logic of dependency or the setID OR unit is a smart functionality as well.
I think my main concern at this point is that it would be a pretty sizeable breaking change - or would require two parallel operating versions (one with the category as varchar, one with it as a numeric), in order to preserve operation of folks using the previous version. Plus updates to our validation and other support tools. With version 3 still pretty fresh, I wonder if this is something we could put off to a version 4 release, or if you really think this is something potentially urgent?