Re: Making groups

tom.rmadilo wrote:
>> When you insert a NULL value into a
>> database, you have already screwed up.

Darren New wrote:
> Err, no. When you create a customer record, and the customer declines to
> state his or her age, what do you store in the database column?

Let it be clear. I'm not *ignorant* of the position a Relational
Algebra purist would take. I just don't *agree* with it as a
practical matter.

In the case of a customer who refusese to state an age, Relational
Algebra purists would say that you have to create a second table,
mapping customerID onto age, and omit the rows from the second table
corresponding to those customers who have failed to state an age. I
have worked with a DBA who have imposed that sort of requirement. It
was a little weird. If you wanted to have a report showing customer
information (including age if known), you had to LEFT OUTER JOIN the
'customer' table and the 'customer_age' table. Except that the DBA
didn't like outer joins either, because of course they're not pure
according to the Relational Algebra either.

So you had to write

SELECT forename, surname, age
FROM (SELECT forename, surname, age
FROM customer, customer_age
WHERE customer.customer_id = customer_age.customer_id
UNION
SELECT forename, surname, 0 as age
FROM customer, customer_age
WHERE NOT EXISTS (
SELECT 0 FROM customer_age
WHERE customer_age.customer_id = customer.customer_id
)
)
ORDER BY surname, forename

and all to avoid ever having a NULL pass anywhere through the query.
(Oh, by the way, note the SELECT 0 - the DBA also unconditionally
forbade SELECT *, even in an EXISTS clause; he also forbade selecting
any column whose value was not to be used.)

So yes, you can avoid nulls, and the Relational Algebra purists say
you should, but in this particular example, it seems that the
avoidance of nulls is because the extra work is good for the soul, or
something. I never had the practical advantage explained to me in
terms I understood.

--
73 de ke9tv/2, Kevin
.

Relevant Pages

  • Re: All hail Neo!
    ... As far as flexibility goes, ... it, and I have the age of 999 of them, and one null. ... customer base, and what does each decile look like? ... For each decile, there are ...
    (comp.databases.theory)
  • Re: Stroing Custom Fields in database
    ... > I was just giving example for age. ... > repository where we store the field descriptions for the table. ... >> relation table that lists valid combinations of customer and field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Stroing Custom Fields in database
    ... Don't store Age. ... based on DOB. ... relation table that lists valid combinations of customer and field. ... > ContactID ...
    (microsoft.public.access.tablesdbdesign)
  • Re: The Dark Side of Coffeehouses
    ... A customer can ask and a server can say no (or ... vice versa) regardless of age difference. ... > The best way to measure the situation is the girls perspective. ... > Attractive young girls have plenty of experience with guys of all ages ...
    (alt.coffee)
  • Re: Combo Box - Event Not in List
    ... still have the problem of manually deleting the invalid surname entered in ... >> customers surname and customer reference number and then display the ... >> and the Esc key to clear the unlisted data. ...
    (microsoft.public.access.macros)