[dba-SQLServer] ANSI null compliance

Arthur Fuller artful at rogers.com
Mon May 9 18:22:17 CDT 2005


I'll accept that. I just have not worked on such apps recently, except 
in one case, where I accepted the junk data into a temp table and kept 
it there until it passed all the validations... which made it brain-dead 
simple to list the problem records.

But in general I have trouble accepting your proposition. Not saying 
it's incorrect, but only that I have trouble with it....

tblEmployees:
EmpID - autonumber/identity
Surname - obviously not null (ONN)
Given name - ONN
Address - ONN or you hire the homeless
City - ditto
Number of Children -- she doesn't know?
Previous Employer -- nullable

tblProducts:
ProdID - identity
Name - how can you sell it without a name? ONN
CostPrice - admittedly could be variable but if it's zero then you're 
going to be a trillionaire if you sell enough of them.
Vendor - could be multiple in which case a child table but assuming only 
one, you gotta buy it from somebody.
Re-Order Point - ONN; default it to a dozen or whatever, based on your 
experience, but don't permit a null.

tblOrders:
OrderID - identity
SalesPerson - if you permit null here then profit sharing is obviously 
not part of the scheme, in which case why bother recording it?
OrderDate - default GetDate()
SalesOffice - if you permit null here, why bother recording it?

and so on.

With Codd, Date, Pascal and Celko, to name just a few experts in the 
field, I think there is a problem with nullable columns. In general they 
indicate that the database model has not been sufficiently considered.

As to the zero'th row concept, I agree that "unknown and to be addressed 
later" is a valid concept, but I disagree that its number should be 
zero. That's all. Why? Because it breaks the relational model's most 
fundamental premise -- identity keys should be meaningless, and their 
cardinal and ordinal orders should be irrelevant to the app. This is as 
fundamental, IMO, as saying that customers whose company name begins 
with "A" should be in the PK range of 1-1000. I should be able to add 
"Employee Department Not Yet Assigned" after having added 100 
departments. I shouldn't have to make it the zero'th row. IMO that 
breaks all the rules, and if you believe that kind of falsification you 
might as well dump the relational model and either go back to Codasyl or 
forward to OODB.

Arthur
- s/he doesn't know? Default zero.
Previous Empl
Stuart McLachlan wrote:

>On 9 May 2005 at 17:49, Arthur Fuller wrote:
>
>  
>
>>You have just cited the exceptions which IMO prove my point. Those 
>>columns should be nullable for exactly the reasons you mention. 
>>    
>>
>
>In my experience, far more fields need to be nullable than not, so the 
>default is appropriate in my case.
>
>  
>
>>My point 
>>was that an employee without a surname is a problem, and similarly 
>>without an SSN or SIN or their international equivalents. 
>>    
>>
>
>No such international equivalent in PNG :-(  
>
>Personnel databases are a nightmare here, not only is ther NO unique 
>identifier such as an SSN (there is no Social Security), people change 
>there names at will (often when someone dies, their nephew will adopt their 
>name etc), in many areas, they use only a few distinct names within one 
>locality and they interchange first and last names ( Tau Buruka is Buruka 
>Tau's son, there are lot's of  others with either Tau or Buruka as a last 
>name)
>
>A significant proportion of the population, especially the older generation 
>don't even knwo the year of their birth, let alone the date. Very few 
>people have birth certificates.
>
>Add the fact that employers frequently recruit staff using the "wantok"* 
>system so a lot of the staff come from the same locality, are related and 
>have similar names.
>
>*Wantok = "one talk" literally a person who speaks the same language as 
>you; there being about 800 languages in PNG.  Effectively a wantok is a 
>relative or someone who comes from the same tribe/clan/localtiy as you do.  
>Without any form of government social security and being a very triabal 
>society, you have to look after your wantoks and you expect them to look 
>after you.
>
>  
>
>>Certain data 
>>must be there. Other data is optional. But in the case that you cited 
>>(missing home phone) I would try to devise a system (stored proc or 
>>whatever) that assigned this row a status of "missing important data" 
>>and automatically revisit these faulty rows so they can be corrected.
>>
>>    
>>
>
>I find it much easier to just use the built in staus of "no data value" ie 
>Null.   It is then easy to identify the fields which contain "important 
>data" and list those records with Nulls.   No need to develop any 
>system(stored proc or whatever). 
>
>  
>
-------------- next part --------------
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005


More information about the dba-SQLServer mailing list