What exactly does "not see" mean? Some use tab, others use the mouse. That doesn't tell us much and there are better ways to track user hand/mouse movements if you need such telemetry. UI api's often handle nulls/blanks different such that you don't want to over-rely on how one of them does it. Multi-screen tasks should track when each sub-screen is finished, not just the final transaction. I'd like to see a more specific use-case.
Re: Did you want to update the field to null or not update it at all is another one.
Who, the user or DBA? And why? Users don't know a Null from a horse; you shouldn't make them think about that, that's an internal guts thing.
I have to say I'm a bit amused at the insistence on tabs/mice/telemetry :)
In at least one project I worked on, there was a lot of survey data entered from paper surveys used in the 90s and early 2000s. The structure of the survey included pass-through questions: "If you have ever smoked a joint, please check yes and answer questions 42 and 43. If you have not ever smoked a joint, please check no and TURN THE PAGE."
One can certainly build a logic to process these replies (check answer to question 41, tally blanks in questions 42, 43 according to answer to question 41) but since these questions and answers were also entered into the computer in the olden days, NULL was used if questions 42, 43 were passed through, while blank was used if questions 42, 43 were left blank (and 41 was yes).
Was the user prompted to enter the value but left it blank or where they never prompted. Yes you could always have more data, but this extra two value distinction is common and practical in my experience.
On updating its related to prompting typically, again communicating user intent, did the user update a field from having a value to not having a value (they purposely blanked it out) or they didn't touch the field and maybe it wasn't even shown so don't modify it. Basically am I going to generate a update statement with set field = null or no set at all for that field. This is trivial in json to send through application due to null vs undefined.
Re: but this extra two value distinction is common and practical in my experience.
I'm still not following. What exact hand or keyboard/mouse movements constitute "left it blank"? "Leave" means to "move away from". What is moving away from what?
Usually one stores a time-stamp for input prompts (screens). If the prompt never appears, you then have no time-stamp and/or record for it. Why would a broken prompt create a record to begin with? I'm missing some context you seem to be assuming. You may be assuming behavior of a specific UI product or stack.
Not sure I can explain more simply, just think multi part wizard interfaces or forms with button that open up sub-forms.
I think you get it since you talk about time stamps. Just as you could record all mouse movements and keyboards to get higher fidelity you could break a single record into multiple with times stamps to record each step in a form, then the lack of record would be distinct from a record with all nulls along with time stamps. You could also do a record per field (which I have seen more than once) with metadata per field as other columns.
But without all that a system that supports null and some empty value gives you more fidelity than just null that again in my experience is practical.
Empty string and null work fine with strings for this purpose but for other datatypes you start needing to pick special values like the minimum value for a 32 bit int or 0 date etc.
Usually you don't get the entire sub-screen's data such that a blank versus null distinction wouldn't help. You can't "half submit" a dialog or sub-screen on the vast majority of systems I ever worked with it, and one adds time-stamps to critical sub-screens if they can.
Maybe you encountered a specific product's edge-case where it helps, but I'd rather trade away 9 other headaches to get your edge case headache. It may be the case that Product X doesn't allow time-stamps, but should we gum up all strings to make just Product X happy? I don't see how the tradeoff math favors that. Plus, it encourages bad designs by plugging problems with funny non-printable codes. Let's not enable slop-heads.
Re: but for other datatypes
For the purpose of this discussion I'm limiting it to strings, where the majority of null-related headaches are found in my experience.
Re: Did you want to update the field to null or not update it at all is another one.
Who, the user or DBA? And why? Users don't know a Null from a horse; you shouldn't make them think about that, that's an internal guts thing.