Hi,
There are NA values in columns like addressesOrder in parts table. However these columns are “int” in the ERD. Int cannot store NA value.
The same also happens for minValue, maxValue, minLength, maxLength in part table. These four one can also contain “seeUnitVal”.
Shouldn’t these columns be varchar?
Thanks.
Hello! Thank you for this.
This is tangential to some planned adjustments I was looking to roll out possibly in V3.0.1.
They have been classified as int because the main purpose of these columns is to store numeric information (and for the xxOrder columns, this information only for a minority of rows). In R (my native coding language) NA is a missing value that can be recorded with integer values because it just marks an absence. When drafting the SQL schema drafts and seed data for version 3 (available here), I changed those values to NULL instead, because that’s what SQL uses instead of NA. minValue, maxValue, minLength, and maxLength should all probably be varchar though, because of the seeUnitValue.
Do you have a preference of NULL versus NA in these situations? I am thinking that the dictionary in excel/various css should try to more closely follow SQL so I’m thinking of changing some of these NA values to NULL.
Hi,
I do not have preference. The only attention point I have is that NULL can mean missing. If you need to differentiate missing from not applicable then you have to use a varchar with NA. Otherwise NULL can be used I think.
A fair point! One for discussion - @dmanuel, if you follow.
I had a second though about this. NULL is probably good enough for the majority of tables in the ERD. Otherwise that would require to complete all variables with N/A (as mentioned in the other post you could use N/A instead of NA to avoid any overlap with countries abbreviation) when the context is not applicable which would be a very cumbersome approach.
I would nevertheless make exception for specific cases when the use of N/A is really valuable. The dictionary and the parts table could be an exception. Being at the core of the model it might be wise to keep N/A. It gives extra assurance of the completeness of the dictionary and avoid any ambiguity (missing or not applicable).
That is a really good point. I think you’re right that keeping NA keeps the structure and assures users that fields are intentionally blank. Plus for the SQL schemas, I’m having them automatically generated with the new releases and the Nasty are replaced with “NULL” as needed for the integer fields, but the “source of truth”, being the dictionary, should still maintain the use of NA. I think you’re right.
I would then define all variable of parts table as being character variable
This points to a validation requirement as well as a storage-type question.
@wastewater_belgium, reading the parts CSV as character data would preserve what contributors entered, including NA and values with leading zeros. That is useful at import. We should also retain the meaning of each field: an order value should still be checked as an integer when it applies, and a length limit should be checked as a non-negative integer or an explicitly supported instruction.
We already have missingness parts and sets for this. NA means not applicable, nr means not reported, and other codes distinguish reasons such as data not collected or withheld. A field’s missingnessSet identifies its allowed set. For example, nrNAMissingnessSet contains only nr and NA; the general set is broader. Validation should use the set assigned to that field, rather than accepting every missingness code everywhere.
This also connects to the country-code collision discussion. I proposed checking that a field’s ordinary values and its missingness set do not overlap. NA can otherwise mean either Namibia or not applicable. That check needs an explicit case policy too: nr and NR are different strings unless a consumer treats them as equivalent.
That gives us three things to keep aligned:
There is a definition gap to resolve for the dictionary itself. In the versions reviewed, addressesOrder, minLength and maxLength contain literal NA values, while their definitions have missingnessSet = NA instead of naming an allowed set. We should make the intended permission explicit. Broadening these fields to unrestricted text would leave that validation question unanswered.
For SQL, converting distinct reasons to NULL loses information. The literal ODM code null is also different from database NULL or an empty CSV cell. Where a numeric SQL column is needed, we should document the conversion and preserve the reason separately if the distinction must survive export.
Similarly, seeUnitVal is a reference to another constraint. Accepting arbitrary text would not check whether that reference can be resolved or whether the resulting bound is valid.
The LinkML generator already attempts to combine a field’s ordinary range with its selected missingness set. We need to test that both remain valid, that codes outside the selected set fail, and that unexplained blanks and unit-based bounds are handled explicitly.
Would those distinctions meet your database needs? An example where an SQL import must preserve “not applicable” separately from missing would help us test the approach.T