RE: Counterquestion - is Oracle a He or a She - or an It or a hermaphrodite

I think the two of you are disagreeing because you are discussing the problem at two different levels:

1. Business requirements
2. Physical database implementations

There are several values that might apply IN BUSINESS TERMS:

1. someone's actual middle name
2. NULL, meaning they might or might not have one, and if they do have one, it is unknown.
3. Not Applicable, meaning they don't have one.
4. More esoteric, but "I know they have a middle name, but I don't know what it is". The CIA might need this level of detail about people they are tracking, almost all businesses don't!

I've seen several others in various lists over the years.

The degree of detail needed depends upon the business situation and the problem to be solved.

In ORACLE DATABASE TERMS, we don't have a standardized way built into the database language to distinguish between points B and C above.

There are several ways to deal with it in physical implementation terms (and this isn't an all-inclusive list, either):

1. Get over it. It's not worth the time or trouble. For most situations, that is exactly the correct IMPLEMENTATION solution. It's not the correct BUSINESS solution, just the least worst.
2. Invent a value that means "Not Applicable". Of course, it needs to be a value that no legitimate data would have. And, of course, EVERY SINGLE PROGRAM that accesses that data needs to know how to handle the "special code". Otherwise, letters will get mailed out to me addressed to "David Not Applicable Wendelken", which would somewhat lessen my estimation of the company doing the mailing.
3. Add a column called "Middle_Name_Really_Blank_YN". Of course, you'll need to force it to be N when someone fills in the Middle Name field.
4. Ad nauseum.

>-----Original Message-----

>> Quite simply, and the most easily understood example I know, some
>>people have no middle name.
>
>Which means a non-existant middle name. No value. In
>programming terms that is called a null.
>
>> In that case, the value of the string of that person's middle name
>>is not NULL, but rather the accurate value for the person's middle name is
>>the empty string.
>
>Disagree!
>
>> The value is absolutely known, whereas the NULL value is definitely
>>not known.
>
>...

--
http://www.freelists.org/webpage/oracle-l