On Thu, Apr 16, 2015 at 11:48 AM, Gene Wirchenko <[email protected]> wrote:
> At 03:23 2015-04-16, Laurie Alvey <[email protected]> wrote: > >> In my innocence, I wonder why you want to disallow NULLs? They are, IMHO, >> the best way to hold unknown or irrelevant column values. >> > > They get ambiguous. Take your example above. Does a null mean > unknown or irrelevant? The two are different cases. e.g. A employee's > salary might be unknown, or the employee might not be salaried. > > You can always record explicitly that something is unknown if you > want. > > Nulls poison expressions. The moment you have the possibility of a > null, you have to code handle nulls. Yes, the tables may be less complex, > but that complexity then has to be dealt with in the app code again and > again and again. > > Due to an error in SQL, summing a column in a table where there are > no matching rows gives a result of null. It should give a result of 0, > that being the identity element for addition. Because of this one glitch, > I have had to adjust results in several places in my system, replacing null > with 0. > ----------- Null allows poor data design to stay broken instead of fix the issue at hand on small to large sized systems. 15 years ago I had a patient treatment system for taking results through various cancer trials. You had original poor data that had for simplicity sake a BloodTest table with 12 or 16 columns. Of course not every sample of blood needed all those tests. Any particular Trial would cycle through the necessary tests at various points of the treatment and sometimes they were only needed 2 components of the 12 tests. Had this been done in a normalized Test Results environment each test would only have the data recorded by the research tech and not had millions of nulls for never intended results. Table layout of TestID INT TestTypeID INT TestResultNumeric Float TestResultChar String Notes String Researchers and their techs were verified on the quality of notes as documentation of the patients progress and possible altercations that were possibly happening. When there were 12 tests on a single line attempting to cross reference the text in the Note against a specific test and it's result was a RPITA. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJidMYJCenO42j=ehksanmfpdvx4hrwbzccsg3uq14vsqm_...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
