Sic Semper NULL

Question

Sic Semper NULL

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Donald Burr Ten Centuries Points: 1056 More actions · Answer 1

"On the other hand, there are those who would recommend a less dramatic method of dealing with NULL, namely: "learn how to use them properly, and minimize their use where practical."

WOW FINALLY, Someone to voice and understand the true need, and nature of NULL.

I have long argued that there are real world needs for null when the data is truly not known. As an auction company we take in 10's of thousands of items each year, and write detailed condition reports on them. We also create highly detailed attribute lists as part of the condition report.

So as an example if we created defaults of say "No" for an attribute like "Air Conditioning", it could lead to potentially damaging results.

If the condition report writer failed to check the A/C, there would be nothing to alert anyone that the attribute had been missed, as it would look like the asset does not have A/C. If the item does in fact have A/C and the writer simply missed it the end result would be a costly devaluation of the asset. In this case NULL, is valuable, and with 148 distinct attributes for a Truck Tractor or Trailer, as an example, I can only imagine (or rather can't imagine) the nightmare of maintaining and querying this in 6NF. While it is true that this could be handled in the interface by say not allowing the input of a condition report until all the questions are answered, there are distinct and real business reasons why you would not want do that, so null becomes the best solution. The only other course is to set the defaults to "Unanswered" but, then what about numeric fields?

Clearly; when appropriate, defaults and normalization should be used, but I think it was quite elegantly proven in this article that when used thoughtfully and with careful design NULL is a very powerful tool!

MudLuck SSCrazy Points: 2301 More actions · Answer 2

Sorry to open an old thread but I'm fishing for some perspective and hope there are pople out there that can help.

I have several inhouse DB's that long ago someone diecided are better off setting to all not null for columns. The way they were able to do this was by setting everything to not null then defining a default value. For numbers they use -1, strings '', and dates 1/1/1900. From a administration point of view and perfromance point of view it's a freakin night mare for me. To do things like add a column to a 120 gig table as not null with a defualt can take hours of downtime, reindexing data takes a day, and statistics on column and therefore optimizer selection well is skewed to say the least.

The other day I put the foot down they were adding a boolean field of sorts of type Char (1) they had it set to not null and populated 'N' as default. I told them no set it null and when you turn it on then update to 'Y'. What if anything is wrong with that suggestion? What are peoples opinions on utiizing fake data as place holders for nulls?

Thanks for the feedback in advance.

MudLuck

Donald Burr Ten Centuries Points: 1056 More actions · Answer 3

This first problem is the char(1) field of 'Y' and 'N'

They should be using a bit field of 1 and 0, its faster, takes less room and has a speicalized indexing routine.

Next, if the natural state of logic for the application is False or True then it is proper to set a default of False or True, however if the true state of the answer is absolutely not known at the time then NULL is the correct choice. It is standard practice to intialize bool's as false. That said, we initialize a number of our bools as NULL because of the following business reason. We have very complex attribute/condition collection forms. There are a lot of Y/N questions on the form that ALL require an answer. The actual state of the answer is not known until the report is completed. We initialize the fields with NULL, so that we can detect an UNANSWERED question. If we initialized the column as either True/False, Y/N. Yes/No, there would be no way to tell the difference between a "missed" answer and a default value. In this way any remaining NULL means that someone needs to get the answer.

It really really depends on the business need and the reason for the default. Merely setting arbitrary default values to eliminate NULLs because someone once heard that NULLs are bad is just as wrong as leaving everything NULL when a default is appropriate.

It is not an exact science which is why the NULL battle still rages on. I hope this helps a bit

bradcapo2 Grasshopper Points: 13 More actions · Answer 4

This is an interesting post.. thank you for sharing

plan solution commission de surendettement[/url] - commission de surendettement, vous pouvez demander un dossier de surendettement.plan solution commission de surendettement[/url]

bradcapo2+1 Grasshopper Points: 21 More actions · Answer 5

Great work .. really informative .. and thanks a lot for sharing

calcul credit immobilier courtier taux simulation de france pretUne simulation credit immobiler de France a faire un pret.calcul credit immobilier courtier taux simulation de france pret