March 3, 2017 at 12:45 pm
roger.plowman - Friday, March 3, 2017 9:40 AMpatrickmcginnis59 10839 - Friday, March 3, 2017 8:31 AMNulls are an abomination.
The problem is their ambiguity. Not in the literal sense of "data is missing" but in the sense of "WHY is this data missing".
I know I'm probably going to become a pest in this thread, but SQL Server isn't there to tell you why it is missing. Its like your storing an integer value of 200 for instance and yelling at SQL for not telling you 200 of WHAT. You named the column, you need to document the cases that could cause data not to be stored. If you want to know WHY, add a WHY column and fill THAT in.
In one way I agree, but in the case of null it crosses the line. The problem is data integrity, which is very much a SQL Server/persistence layer issue.
Null means "missing". But in terms of database design/validation/data integrity/consistency "missing" is unacceptably ambiguous.
Consider a field that needs to be filled in to let a process occur. For example, a parts order. Clearly if you order a part leaving the part number null ("missing") you cannot process the order.
From a simple "bucket" perspective you are correct, a missing part number concerns SQL Server not at all. But from an architectural perspective an order without a part number is utterly unacceptable.
That's the most extreme case, missing data that prevents correct operation, but even more subtle issues rear their ugly heads.
For example, an employee's termination date is null. Does that mean the employee is still employed with us? Or does it mean they've been terminated and somebody forgot to fill in the date? Or did it happen so long ago we no longer know the termination date?
Typically a null termination date would mean the employee is still employed--but if they had been terminated then clearly some kind of error occurred. But given the data we have no way of determining what kind of error.
Using magic values for the conditions "to be determined (TBD)", "not applicable" (N/A) or "verified unknown" (UNK) instead of allowing nulls we can now say that an employee with a termination date of:
N/A - is still employed, or if not the error was failure to correctly update the field
TBD - Employee IS terminated, but the exact termination date has yet to be determined
UNK - The employee IS terminated but the exact termination date has been lost for some reason (usually because it happened before the data was required to be kept).Dates are perhaps the biggest reason to use magic values, but just about any domain can benefit from them, assuming the sub-domain (the range of actual valid values) is smaller than the domain (the entire possible range of values).
A "why" column adds complexity and size, especially considering we're speaking of domains here, not individual fields. You'd basically need a why column (or table) for every column in your database! Unacceptably complex and cumbersome, I think...
If a particular column, such as the PartNumber in your example, makes no sense to have a null value then you declare the column as NOT NULL. That solves the null problem for that column as the data for that row can't be saved if the a value is not provided.
Nulls, just like cursors, have a place to be used. You need to know when to use them and how to use them properly. This also means working closely with the developers that will be using the database and making it clear between all parties what is being done and why.
March 3, 2017 at 12:59 pm
Lynn Pettis - Friday, March 3, 2017 12:45 PMroger.plowman - Friday, March 3, 2017 9:40 AMpatrickmcginnis59 10839 - Friday, March 3, 2017 8:31 AMNulls are an abomination.
The problem is their ambiguity. Not in the literal sense of "data is missing" but in the sense of "WHY is this data missing".
I know I'm probably going to become a pest in this thread, but SQL Server isn't there to tell you why it is missing. Its like your storing an integer value of 200 for instance and yelling at SQL for not telling you 200 of WHAT. You named the column, you need to document the cases that could cause data not to be stored. If you want to know WHY, add a WHY column and fill THAT in.
In one way I agree, but in the case of null it crosses the line. The problem is data integrity, which is very much a SQL Server/persistence layer issue.
Null means "missing". But in terms of database design/validation/data integrity/consistency "missing" is unacceptably ambiguous.
Consider a field that needs to be filled in to let a process occur. For example, a parts order. Clearly if you order a part leaving the part number null ("missing") you cannot process the order.
From a simple "bucket" perspective you are correct, a missing part number concerns SQL Server not at all. But from an architectural perspective an order without a part number is utterly unacceptable.
That's the most extreme case, missing data that prevents correct operation, but even more subtle issues rear their ugly heads.
For example, an employee's termination date is null. Does that mean the employee is still employed with us? Or does it mean they've been terminated and somebody forgot to fill in the date? Or did it happen so long ago we no longer know the termination date?
Typically a null termination date would mean the employee is still employed--but if they had been terminated then clearly some kind of error occurred. But given the data we have no way of determining what kind of error.
Using magic values for the conditions "to be determined (TBD)", "not applicable" (N/A) or "verified unknown" (UNK) instead of allowing nulls we can now say that an employee with a termination date of:
N/A - is still employed, or if not the error was failure to correctly update the field
TBD - Employee IS terminated, but the exact termination date has yet to be determined
UNK - The employee IS terminated but the exact termination date has been lost for some reason (usually because it happened before the data was required to be kept).Dates are perhaps the biggest reason to use magic values, but just about any domain can benefit from them, assuming the sub-domain (the range of actual valid values) is smaller than the domain (the entire possible range of values).
A "why" column adds complexity and size, especially considering we're speaking of domains here, not individual fields. You'd basically need a why column (or table) for every column in your database! Unacceptably complex and cumbersome, I think...
If a particular column, such as the PartNumber in your example, makes no sense to have a null value then you declare the column as NOT NULL. That solves the null problem for that column as the data for that row can't be saved if the a value is not provided.
Nulls, just like cursors, have a place to be used. You need to know when to use them and how to use them properly. This also means working closely with the developers that will be using the database and making it clear between all parties what is being done and why.
Oddly enough, I agree with you (My point was speaking to one particular point by a poster). It's just my view of Null's niche is far more restrictive than most people. For me the ONLY use of null is in a (N)VARCHAR(MAX) field used for human-only notes where the majority of the notes are supposed to be empty (i.e. to save disk space).
Otherwise the value of a way to distinguish flavors of null makes me fall on the side of domain-level magic values.
March 3, 2017 at 1:47 pm
roger.plowman - Friday, March 3, 2017 12:59 PMLynn Pettis - Friday, March 3, 2017 12:45 PMroger.plowman - Friday, March 3, 2017 9:40 AMpatrickmcginnis59 10839 - Friday, March 3, 2017 8:31 AMNulls are an abomination.
The problem is their ambiguity. Not in the literal sense of "data is missing" but in the sense of "WHY is this data missing".
I know I'm probably going to become a pest in this thread, but SQL Server isn't there to tell you why it is missing. Its like your storing an integer value of 200 for instance and yelling at SQL for not telling you 200 of WHAT. You named the column, you need to document the cases that could cause data not to be stored. If you want to know WHY, add a WHY column and fill THAT in.
In one way I agree, but in the case of null it crosses the line. The problem is data integrity, which is very much a SQL Server/persistence layer issue.
Null means "missing". But in terms of database design/validation/data integrity/consistency "missing" is unacceptably ambiguous.
Consider a field that needs to be filled in to let a process occur. For example, a parts order. Clearly if you order a part leaving the part number null ("missing") you cannot process the order.
From a simple "bucket" perspective you are correct, a missing part number concerns SQL Server not at all. But from an architectural perspective an order without a part number is utterly unacceptable.
That's the most extreme case, missing data that prevents correct operation, but even more subtle issues rear their ugly heads.
For example, an employee's termination date is null. Does that mean the employee is still employed with us? Or does it mean they've been terminated and somebody forgot to fill in the date? Or did it happen so long ago we no longer know the termination date?
Typically a null termination date would mean the employee is still employed--but if they had been terminated then clearly some kind of error occurred. But given the data we have no way of determining what kind of error.
Using magic values for the conditions "to be determined (TBD)", "not applicable" (N/A) or "verified unknown" (UNK) instead of allowing nulls we can now say that an employee with a termination date of:
N/A - is still employed, or if not the error was failure to correctly update the field
TBD - Employee IS terminated, but the exact termination date has yet to be determined
UNK - The employee IS terminated but the exact termination date has been lost for some reason (usually because it happened before the data was required to be kept).Dates are perhaps the biggest reason to use magic values, but just about any domain can benefit from them, assuming the sub-domain (the range of actual valid values) is smaller than the domain (the entire possible range of values).
A "why" column adds complexity and size, especially considering we're speaking of domains here, not individual fields. You'd basically need a why column (or table) for every column in your database! Unacceptably complex and cumbersome, I think...
If a particular column, such as the PartNumber in your example, makes no sense to have a null value then you declare the column as NOT NULL. That solves the null problem for that column as the data for that row can't be saved if the a value is not provided.
Nulls, just like cursors, have a place to be used. You need to know when to use them and how to use them properly. This also means working closely with the developers that will be using the database and making it clear between all parties what is being done and why.
Oddly enough, I agree with you (My point was speaking to one particular point by a poster). It's just my view of Null's niche is far more restrictive than most people. For me the ONLY use of null is in a (N)VARCHAR(MAX) field used for human-only notes where the majority of the notes are supposed to be empty (i.e. to save disk space).
Otherwise the value of a way to distinguish flavors of null makes me fall on the side of domain-level magic values.
I'm not disagreeing that if you need to have a value to proceed then null would be inappropriate. Remember your original point is that nulls themselves are an abomination and should be replaced with "magic values" and in fact "magic values" would be sufficient to proceed in all cases yet nulls are not. Yet the implication with the phrasing of your post is that a "magic value" in a part number would be sufficient to continue with placing and completing an order because it isn't null. I would have problems completing an order with an part number listing of 'N/A' or 'TBD' if they're "magic values" implying "not applicable" or "to be determined." If they're actual part ids then I would not want to call them "magic values" because that phrase has a commonly accepted meaning that could be interpreted in this case as "not an actual product number," and indeed I'd probably have problems selling a part called "N/A" LOL
March 3, 2017 at 2:16 pm
patrickmcginnis59 10839 - Friday, March 3, 2017 1:47 PMroger.plowman - Friday, March 3, 2017 12:59 PMLynn Pettis - Friday, March 3, 2017 12:45 PMroger.plowman - Friday, March 3, 2017 9:40 AMpatrickmcginnis59 10839 - Friday, March 3, 2017 8:31 AMNulls are an abomination.
The problem is their ambiguity. Not in the literal sense of "data is missing" but in the sense of "WHY is this data missing".
I know I'm probably going to become a pest in this thread, but SQL Server isn't there to tell you why it is missing. Its like your storing an integer value of 200 for instance and yelling at SQL for not telling you 200 of WHAT. You named the column, you need to document the cases that could cause data not to be stored. If you want to know WHY, add a WHY column and fill THAT in.
In one way I agree, but in the case of null it crosses the line. The problem is data integrity, which is very much a SQL Server/persistence layer issue.
Null means "missing". But in terms of database design/validation/data integrity/consistency "missing" is unacceptably ambiguous.
Consider a field that needs to be filled in to let a process occur. For example, a parts order. Clearly if you order a part leaving the part number null ("missing") you cannot process the order.
From a simple "bucket" perspective you are correct, a missing part number concerns SQL Server not at all. But from an architectural perspective an order without a part number is utterly unacceptable.
That's the most extreme case, missing data that prevents correct operation, but even more subtle issues rear their ugly heads.
For example, an employee's termination date is null. Does that mean the employee is still employed with us? Or does it mean they've been terminated and somebody forgot to fill in the date? Or did it happen so long ago we no longer know the termination date?
Typically a null termination date would mean the employee is still employed--but if they had been terminated then clearly some kind of error occurred. But given the data we have no way of determining what kind of error.
Using magic values for the conditions "to be determined (TBD)", "not applicable" (N/A) or "verified unknown" (UNK) instead of allowing nulls we can now say that an employee with a termination date of:
N/A - is still employed, or if not the error was failure to correctly update the field
TBD - Employee IS terminated, but the exact termination date has yet to be determined
UNK - The employee IS terminated but the exact termination date has been lost for some reason (usually because it happened before the data was required to be kept).Dates are perhaps the biggest reason to use magic values, but just about any domain can benefit from them, assuming the sub-domain (the range of actual valid values) is smaller than the domain (the entire possible range of values).
A "why" column adds complexity and size, especially considering we're speaking of domains here, not individual fields. You'd basically need a why column (or table) for every column in your database! Unacceptably complex and cumbersome, I think...
If a particular column, such as the PartNumber in your example, makes no sense to have a null value then you declare the column as NOT NULL. That solves the null problem for that column as the data for that row can't be saved if the a value is not provided.
Nulls, just like cursors, have a place to be used. You need to know when to use them and how to use them properly. This also means working closely with the developers that will be using the database and making it clear between all parties what is being done and why.
Oddly enough, I agree with you (My point was speaking to one particular point by a poster). It's just my view of Null's niche is far more restrictive than most people. For me the ONLY use of null is in a (N)VARCHAR(MAX) field used for human-only notes where the majority of the notes are supposed to be empty (i.e. to save disk space).
Otherwise the value of a way to distinguish flavors of null makes me fall on the side of domain-level magic values.
I'm not disagreeing that if you need to have a value to proceed then null would be inappropriate. Remember your original point is that nulls themselves are an abomination and should be replaced with "magic values" and in fact "magic values" would be sufficient to proceed in all cases yet nulls are not. Yet the implication with the phrasing of your post is that a "magic value" in a part number would be sufficient to continue with placing and completing an order because it isn't null. I would have problems completing an order with an part number listing of 'N/A' or 'TBD' if they're "magic values" implying "not applicable" or "to be determined." If they're actual part ids then I would not want to call them "magic values" because that phrase has a commonly accepted meaning that could be interpreted in this case as "not an actual product number," and indeed I'd probably have problems selling a part called "N/A" LOL
Perhaps I conflated two subjects awkwardly. 😀
In the part # example I was disputing that null was acceptable because SQL Server wouldn't care if it was null. You're correct that the use of domain values for the part # of TBD/NA/UNK would absolutely be unacceptable. In a case like that there would probably be referential integrity to a parts table with some kind of identifier key. In cases like that my magic values are always RID's -2, -1, and 0, with "real" part numbers by definition having a RID (identifier) of at least 1. Then a simple constraint on the part # field disallows magic values without destroying the ability of a part # field in a (different) table that might actually need the magic values for some reason.
Perhaps a better example would be sites (physical locations) where you send a truck. The site may have an ATM, a depository (that doesn't give cash), or be a bank (without an ATM). In that case the ATM column could legitimately be "N/A" in the case of a bank or depository. TBD would also be a valid flag (during initial setup), but UNK would not (presumably). ATMs would probably be their own table, of course.
Again, a simple constraint suffices to preserve data integrity (as opposed to referential integrity :))
In either case, no nulls allowed.
In the termination date example Null isn't allowed either, and the ability to determine why is preserved by allowing storage of magic values but still lets you have a "nuanced null" capability.
Sorry for the confusion!
March 3, 2017 at 3:32 pm
As far as magic numbers are concerned about the only ones that have ever made sense to me are for date and time keys for dimensional data modelling.
In OLTP systems I make an effort to design out nulls so that they are the exception rather than the rule. The problem with making a new column not NULL is that on a large table you can bring your DB to it's knees, blowing the log file in the process.
I think data warehouses have to be treated carefully. The problem that people try to solve is when an end user asks a question such as "What is the average spend where product categories are not in this particular list". It's fantastic that there are tools will realise that this should include null product categories but how common is that behaviour?
Im using RedShift and as a column store updates are painfully slow. An approach I have taken for start and end dates is to have one table with the start date and a 2nd containing the termination date. Another approach is to have an employee journal. A record equals an event in an employee's life with the company. In some respects this is sidestepping the null conundrum. Then again something like RedShift has you designing tables in a denormalised fashion in any case
March 4, 2017 at 1:08 am
Also, in a Data Warehouse I would consider having a dimension entry for "Feature Not Yet Implemented"
March 5, 2017 at 12:16 pm
I think one thing all of this discussion points out is the importance of proper validation of data either at the application level or the database update level. or probably both in order to check internally for validity. For instance, assuming the presence of an employment status, termination date and termination reason, then you validate for the employment status value being NOT NULL and within range, then if the value is anything but actively employed, a termination date and reason are required. Alternatively, if status is active, then termination date and reason must be NULL. Then, of course, the design MUST INCLUDE the capability to handle multiple, possibly non-overlapping and/or non-consecutive periods of employment, with corresponding hire or begin dates. The use of placeholder but logically invalid data such as dates really make this lots more difficult to handle because then you will need to try to validate dates with literal values or ranges to exclude the placeholder dates. Further, if your placeholder date should be 1900-01-01, then how do you handle when the user enters 1901-01-01, an easy mistake. I say we still have a very good case for NULL instead of placeholders values. But we still need strict data validation in any case.
And this all leads me to suggest the existence of a related structure to accommodate multiple employment periods with potential multiple job titles and/or classifications, which I actually did at one employer. This avoids the need for multiple employee id values for the same individual.
This is exactly why we don't want amateurs doing our design.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
March 6, 2017 at 3:52 am
In general I favor "magic values". Nulls are not transportable, being handled differently by different technologies. I also work in data warehousing and Business Intelligence where my users have a more direct access to the data and are less technically skilled (or not at all). The concept(s) of null escape them. But they can look at a hire date of 1900-01-01 or a termination date of 2999-01-01 and quickly infer the meaning, even without referring to the documentation. Counting, sorting, and other aggregations continue to work and generally make sense.
For the discussion of the employment termination date, it seems we might be violating relational design and normal form. Using one column to represent the date someone was terminated AND to indicate if they are currently employed is using that attribute to represent two different things. I've seen people terminated and immediately "hired" back to consult or as part time employees. In some industries layoffs and call backs (re-hires) are common. We really need two, and possibly more attributes (columns) to correctly model the reality.
March 6, 2017 at 4:31 am
skeleton567 - Sunday, March 5, 2017 12:16 PM<snip>
And this all leads me to suggest the existence of a related structure to accommodate multiple employment periods with potential multiple job titles and/or classifications, which I actually did at one employer. This avoids the need for multiple employee id values for the same individual.
This is exactly why we don't want amateurs doing our design.
Precisely this.
While the examples in this thread are just examples, storing multiple event data items in a single data row is almost never a good idea. While it may be simple for a simple developer to implement it's generally only appropriate if the events will never be repeated, require multiple values or any other possibilities outside a prescribed fixed procession of events. In other words, when recording time based events such as changes in employment status or project status changes a separate event table is almost always the correct way to do it, and MS-SQL server 2016 now includes temporal tables which can also help. In this case there is no need to worry about NULL or magic date values because there will always be enough entries to record the data regardless of the status values required and any repeats or loops recorded.
June 26, 2017 at 10:52 am
roger.plowman - Friday, March 3, 2017 9:40 AM
In one way I agree, but in the case of null it crosses the line. The problem is data integrity, which is very much a SQL Server/persistence layer issue.
Null means "missing". But in terms of database design/validation/data integrity/consistency "missing" is unacceptably ambiguous.
Consider a field that needs to be filled in to let a process occur. For example, a parts order. Clearly if you order a part leaving the part number null ("missing") you cannot process the order.
So you replace NULL with multiple magic values describing why the data isn't there. Or you say "we will not allow an order-line which has a NULL part number, and make that column unnullable. The former case needs a lot of code to handle a lot of magic values. It is a serious performance hit because in order to process an order you have to check that the part number is not any of the magic numbers - and you still can't process the order. Or you accept that you will sometimes have cases where you don't know the part number, and will want to monitor them and chase them up, in which case you will allow that column to be NULL and can, if you need it, have a separate "reason for null part number" table with primary key identifying an order-line, and a single field giving teh reason the part number is missing. That costs nothing in order processing, and it doesn't add space for the reason to the order_line table, and it keeps a record of why that line has a null part number. This way you eliminate the ambiguity by using NULL properly at much less processing cost and much cleaner and tidier code than using multiple magic values.
From a simple "bucket" perspective you are correct, a missing part number concerns SQL Server not at all. But from an architectural perspective an order without a part number is utterly unacceptable.
From an architectural complication, magic values are a stupid unneccessary complexity. Refusing to permit the order line to be entered if the part number is not known is by far the best solution unless you need to keep these stange part-unspecified orders for valid business reasons (that is neither the DBA's decision, nor the developer's, it is a matter for business, sales, and marketing people to determine whether or not there is a requirement for these weird orders, as is also the decision whether the reason for the weird line should be recorded); in the sad case that you have to have these weird things entred, using NULL is a vastly better solution and costs less both in performance and in complexity than using several magic values that using magic numbers.
That's the most extreme case, missing data that prevents correct operation, but even more subtle issues rear their ugly heads.
If that's the extreme case it seems there's no imaginable valid reason for preferring magic values to NULLs.
Dates are perhaps the biggest reason to use magic values, but just about any domain can benefit from them, assuming the sub-domain (the range of actual valid values) is smaller than the domain (the entire possible range of values).
Oh, so dates (presumably termination dates, as that's the example you used) are the justification, not part numbers? Which is really the extreme case?
Just as with part numbers, a NULLable termination date together with a separate table of cause with one row for each null termination date leads to better performance and much cleaner code, with a clean separation between code dealing with the strange cases (which are rarely needed) and (for example) the main payroll production code.
I tend to have a few domains that are not very large. Especially Bit, truth value domains, but also char and tinyint domains can be something of a pain if you need to two or more values (and the magic value is clearly less satisfactory that NULL if it's the only one in the domain) and magic values in a float or real domain are an absolute nightmare.
A "why" column adds complexity and size, especially considering we're speaking of domains here, not individual fields. You'd basically need a why column (or table) for every column in your database! Unacceptably complex and cumbersome, I think...
Oh, so you are saying you have to make every column in every table nullable if nulls are used at all, even if they are actually needed for only one column of only one of the tables in the database! That is clearly nonsense (since it forbids nrmalisation as no table can have a primary key). The sane approach is quite different: NULL is allowed only for columns where it is essential (for business reasons) to permit unknown values, and when you approach the issue from that sane viewpoint it quickly becomes clear that magic values lead to a far more complex and cumbersome system than does using NULLs sensibly.
Tom
June 26, 2017 at 11:03 am
steveo250k - Monday, March 6, 2017 3:52 AMIn general I favor "magic values". Nulls are not transportable, being handled differently by different technologies.
I dislike magic values, but I also place a premium on practicality. If this works for you, especially across technologies, that's fine.
I prefer to handle something in this case with a default at xfer or display time, a CASE/SWITCH in the code that would provide a value, but leave the NULL in the source. However, that's me.
June 26, 2017 at 4:37 pm
Rod at work - Friday, March 3, 2017 7:57 AMI've seen 2 practices; using so called "magic numbers" or using nulls. When I first went to work at my previous job, I had very little experience with any SQL DBMS. And we were writing Windows applications for Windows 95/98 using controls that didn't understand the concept of a null for a datetime field. So I came up with the idea of using the date of 1800-01-01 to represent a null datetime. That way we could check for that value in code and force the control to show a blank. But over time the programming environment got better, controls got smarter and the whole point of using some arbitrary value like 1/1/1800 to represent a null became ridiculous. I now look back at that and think it was just a stupid idea. As time went by, and other developers came and went, we always had to go through the process of telling them that 1/1/1800 wasn't a "real" date, that it really meant a null date. It became impossible to get rid of, because of all of the software and reports that we built up around the idea of looking for 1/1/1800 (or 1800-01-01) and replacing that with a null or blank. I look back on that as one of the most boneheaded ideas I ever came up with.However, I've noticed that I wasn't the only one who came up with using magic numbers. At that old job we participated in several Federal grants, where we had to supply the Feds with data to satisfy the grant. In all cases the Feds specified that if any value was unknown, it had to be replaced with 9's. How long the string of 9's it took was dependent entirely upon the length of the field of data the Feds were looking for. Sometimes it was as short as just 99. Other times it was like you said Steve, 99999. As far as I know the Feds still have this thing for 9's.
Well, one of the original advocates of using a default value instead of NULL (perhaps 1st Jan 1901 for dates, -32768 for smallints, and so on) was Chris Date, way back in about 1979 or 1980; after he had talked Ted Codd into having two distinct NULLs in the second manifesto instead of the one NULL Codd had suggested in the mid 70s he had this insane idea that having two NULLs meant you would need an infinite number of distinct NULLs to get a clean representation, and it was therefore impossible to have a clean system with any NULLs because you clearly couldn't have an infinite number of anything. At least he had the sense not to go down the multiple magic values track beloved of so many (CJD's argument that you needed infinitely many distinct NULLs as soon as you had two would apply equally to magic values in a domain) and stuck to a single magic value (which would be the default value) for each domain. Over the years he got more strident about using default values instead of NULL, eventually joining up with Fabian Pascal to generate some extremely vicious and obnoxious rants on that topic. There's maybe a case to argue, but neither religious fervour nor total unwillingness to consider any view that's in the least divergent from the chosen dogma is good computer science or good maths. You're lucky you didn't fall down that hole.
Tom
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply