Jim Lawrence
accessd at shaw.ca
Mon May 9 17:13:13 CDT 2005
Hi Arthur:
I have a database that takes data as it arrives. If the record is not
finished; the minimum required data has not been completed, the 'completed'
flag field is not set. The uncompleted records will continue to display and
'nag' until either completed or deleted. These records are hosted in a table
named 'incomplete'. Though they can still be searched on, they exist in
purgatory; they are just not mixed with the general population.
Depending on where in the application, the user searches for data, dictates
which table is searched first. (With only a few thousand records it really
doesn't matter...)
My two cents worth.
Jim
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Monday, May 09, 2005 2:50 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] ANSI null compliance
You have just cited the exceptions which IMO prove my point. Those
columns should be nullable for exactly the reasons you mention. My point
was that an employee without a surname is a problem, and similarly
without an SSN or SIN or their international equivalents. 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.
Stuart McLachlan wrote:
>On 8 May 2005 at 18:17, Arthur Fuller wrote:
>
>
>
>>Frankly, I'm not up to chapter on verse on SQL 92, although I read it
>>religiously :)
>>
>>My knee-jerk reaction would be that all columns of whatever type should
>>default to NOT NULL, because IMO, Null columns represent a weakness in
>>our design model. I hate Null columns, unless the client can express a
>>clearly articulated set of circumstances in which said column should be
>>null. For example, we hire a new employee and are not sure sure in which
>>department to place her. Given this objection, I would say, why hire her
>>then? Client could answer, Because her qualifications are so good we
>>don't want her to go elsewhere, we'll find a place for her.
>>
>>
>>
>When the new employee says "I've just moved here to start this job. I
>don't know my home phone number yet, the telco are currently installing
>it", do you then say "In that case, we can't create an employee record for
>you and you won't get paid"?
>
>
>
>>Now JWC would slide into his zero'th column argument here, which I
>>flatly reject, but that's another topic.
>>
>>I think all column declarations should default to NOT NULL and force you
>>to do extra work to make them nullable. As I see it, if you can do
>>without the data, then do without it. Conversely, if you need the data
>>then demand it.
>>
>>
>>
>
>You must live in a very comfortable world where every question has an
>answer. In my world, data is frequently incomplete and the availability
>of a null value ("A special value, or mark, that is used to indicate the
>absence of any data value.") is essential.
>
>
>
--
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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com