Having opened my mouth I though that it might be interesting to actually check my databases to see how good I was at enforcing NOT NULL in the design.
There are five databases that I use regularly and their sizes vary in terms of tables and columns from about 3 tables with only 3 columns to 20 tables with up to 20 columns. I found that I did NOT enforce NOT NULL on computed columns where the participating columns were already forced to be not null. I also do not enforce it on temp tables, or those which I use for importing data, and on some old tables from Dos which are now rarely used but I don't want to delete. I have some comments columns which I allow to be null but, generally, I have split these off into idnum and note tables. When disk space was at a premium it was a choice between a few null rows against a table full of idnums - and space won. Otherwise, pretty well every converted table has no nulls and every new table is designed "properly" unless I really cannot find a way around allowing a null. Somebody was asking about date fields and how to get around them. What I do is define a date that is "out of range" - usually 01/01/1900 - and convert that to a text message. I used 1900 because it used to be 00 back in the last century. If I have a date where I know the year but nothing more I use 01/01/yyyy and accept that it could mean the first of January that year but as nothing much happens on that day it probably just means that year sometime. I also use an integer field for a date where the first 4 digits are the year and the fifth & sixth are the month. In this case I use yyyy00 for just the year. I have a conversion table that translates these integers into "Jan 2000" or "Feb 2001" or just "2002" from 200001, 200102 and 200200 respectively. This also checks that the integer is a valid entry so I can't get invalid dates out. In conjunction with the date field I can tell if the 01/01/yyyy is real or not by whether I know the month in the integer table. So 01/01/2002 in the date with 200200 in the integer tells me that the date is estimated whereas 200201 in the integer would probably confirm the date as "real". Also, 190000 means really totally "unknown" to me! I know that it's not a perfect system but it works well enough for me. If I needed more accuracy I could expand the integer field to include the days and use, say, 20020700 for an unknown day in July 2002. And Bill, I like your VARCHAR but, surely, you can embed "Unknown" into that rather than having a null? Regards, Alastair. ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 9:20 PM Subject: Re: EQNULL > Remember Al Berry's comment about a column that allows nulls is > poor design? In the case of addr1 and addr2, you could always further > normalize the table, since it's any obvious 1 to many relationship, and > have a table for address lines, no nulls necessary. (he he. Just trying > to start a little design war.) > > Actually, I now store addresses in VARCHAR columns, with the cr/lf > embedded, if necessary. No nulls that way, either, unless the address > is truly "unknown." > > Bill > > > On Wed, 24 Jul 2002 15:44:08 -0400, Thomas J Cimicato wrote: > > >I second that motion. A null address line should not be blank filled or > set > >to some value for value's sake. If the address is null, I can check for a > >null value. > > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
