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