Yes, another NULL versus empty string question.
To be sure with the concept that NULL means not set, while empty string means "something that's empty". Here's my problem: When the default value for any column is NULL, how do you permit the user to go in that NULL.
Let us say a brand new user is produced on the system. There's an initial and surname area surname is needed while name isn't. When designing the consumer, the individual might find 2 text inputs, one for first and something for last. The individual selects to simply go into the surname. The name is technically not set. Throughout the place I check the size of each area, setting all fields which are empty to NULL.
When searching in the database, I observe that the name isn't set. The question that immediately involves thoughts are that maybe they never saw the name area (ie, due to a mistake). But this isn't the situation they left if blank.
So, my real question is, how can you decide whenever a area ought to be set to NULL or perhaps an empty string when receiving user input? How are you aware the user wants the area to become not set without discovering focus or maybe they erased something...or...or...?
I'll break the pattern, and state that I'd always employ NULL for zero-length strings, for an additional reasons.
Should you start fine-slicing the implications of blanks, then you definitely need to ensure in some way that each other developer reads and creates it exactly the same way.
How can you alphabetize it?
Are you able to unambiguously determine whenever a user overlooked entering something, in comparison with deliberately departing it blank?
How does one unambiguously query for that difference? Can a question screen user indicate NULL versus. blank using standard input form syntax?
Used, I have never been prohibited from reading through and writing data using default, no surprise behavior by using this rule. If I have required to be aware of difference, I have used a boolean area (that is simpler to map to unambiguous UI products). In a single situation, I made use of a trigger to enforce True => null value, but never first viewed it invoked since the BR layer strained the condition effectively.
When the user offers an empty string, I usually address it as null in the database perspective. Additionally, I'll typically trim my string inputs to get rid of leading/trailing spaces after which look for empty. It is a small win within the database with varchar() types and in addition it cuts down on the cases for search since i have only have to look for
name is null rather than
name is null or name = '' You might go another way, transforming null to ''. In either case, select a way and become consistent.
I rarely use NULL when mentioning to actual data. When employed for foreign secrets, I'd state that NULL applies, but it's rarely valid for user joined data. The main one exception that will most likely show up quite regularly is perfect for dates that do not exist, for example an worker database having a "termination_date" area. For the reason that situation, all current employees must have something of NULL for the reason that area. For keeping them really enter a null value, for that values that really need a null value, I'd put a checkbox near the input area to ensure that the consumer can check it off and on to determine the related value to null (or perhaps in a easier to use manner, none). When enabling the checkbox to create the area to null, the related text box ought to be disabled, and when a null value has already been connected, it will begin as disabled, and just become enabled when the user unchecks the null checkbox.
I keep things simple. Within this situation, I'd result in the first-title column not-nullable and permit blanks. Otherwise, you will have three cases to cope with anywhere you make reference to this area:
- Blank name
- Null name
- Non-blank name
Should you opt for 'blank is null' or 'null is blank' then you are lower to 2 cases. Two cases are much better than three.
To help answer your question: the consumer entering data most likely does not (and should not) know anything by what a "null" is and just how it even compares to an "empty". This problem ought to be resolved cleanly and consistently within the system--not the UI.