Database inconsistencies: empty text values can be stored both as NULL as well as empty string

[stijnvermeeren-swisstopo]

Many text columns in the database are currently nullable. This means that an "empty" value can be stored both as an empty string, as well as a NULL value. However, mostly, there seems to be no semantic difference between a NULL value and an empty string value.

An example is the column borehole.project_name_bho. Here, the PROD database currently has 10211 entries with an empty string (SELECT * FROM bdms.borehole WHERE project_name_bho = '';), and values with 259 (SELECT * FROM bdms.borehole WHERE project_name_bho IS NULL;).

In particular, a NULL value appears in the database when creating a new borehole in the UI.
An empty string appears in the database when setting a value in editor mode, saving the borehole, then removing the value again, and saving the borehole again.

I think that it would make more sense to change all NULL values in the affected columns to an empty string, and then to make the column not nullable (with empty string as the default value). Then an empty text value would always be represented by an empty string in the database, reducing the risk of unexpected/inconsistent database query results.
(Note: a few text columns are already defined as "not nullable" like this, e.g. casing.name and section.name.)

An incomplete list of potentially affected database columns:

Table	Column	NULL values in PROD	empty string values in PROD
borehole	project_name_bho	✅	✅
borehole	original_name_bho
borehole	alternate_name_bho
borehole	remarks_bho	✅	✅
borehole	country_bho
borehole	canton_bho
borehole	municipality_bho	✅
casing	notes		✅
completion	name
completion	notes		✅
facies_description	description	✅	✅
instrumentation	name
instrumentation	notes		✅
layer	uscs_original_lay	✅	✅
layer	original_lithology	✅	✅
layer	notes_lay	✅	✅
lithological_description	description	✅	✅
observation	comment		✅
stratigraphy	name_sty	✅
stratigraphy	notes_sty	✅
workflow	notes_wkf	✅	✅

There are a few cases of nullable text columns where there might actually be a semantic difference between NULL and empty string. Maybe codelist.text_cli_de is such an example ("no label available for German, fallback to English" versus "use an empty string as the label for German"?). This needs to be looked at by someone who is more familiar with the Boreholes application code base.