Re: does a table always need a PK?

Bob Badour wrote:
>
> "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
> news:3F559CE8.33AA_at_ix.netcom.com..
> > Bob Badour wrote:
> > >
> > > "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
> > > news:3F553DDD.50EB_at_ix.netcom.com.
> > > > Marshall Spight wrote:
> > > > >
> > > > > I am no fan of null, but even if we accept the idea of null, isn't
> > > > > it possible to get whatever behavior we need by an appropriate use
> > > > > of CASE or WHERE NOT NULL or some such?
> > > >
> > > > Yes, you can, but it does make for more complicated,
> > > > error-prone formulations.
> > >
> > > I suggest that it is better to have complicated error-prone formulations
> > > with simple correct formulations than simple error-prone formulations. I
> > > also suggest it is better to offer an obviously unexpected result when
> > > the result is likely unexpected.
> >
> > I don't see the simple correct formulations you refer to. I said they
> > will be more complicated. I show some examples in my online paper --
> > http://www.firstsql.com/idefend.htm.
>
> When the user writes a simple formulation that correctly returns NULL, the
> user will have to think about the issue and decide what the formulation he
> really meant. When the user writes a simple formulation that incorrectly
> returns a number, the user will have no reason to question the result and no
> reason to think whether the result is the desired result.
>
> > As I pointed out above, I don't see it as an "obviously unexpected" result.
>
> If the user expects a number but gets a NULL, the user will find the
> unexpectedness obvious. If the user expects one number but gets another
> number, the user will find the unexpectedness less obvious.

Ok, I guess I was being dense. I understand what you are getting at.

However, it does not cover the general case, making it a bad choice.

For example,

  select AVG(A), AVG(B) from T;

... where I want AVG(A) computed only for rows where A definitely has a value and the same for AVG(B).

Note: for the show-offs out there, here's a meater example:

  select C, AVG(A), AVG(B) from T group by C;

> > It is a complicated area which users must be informed about.
>
> I think that's easier said than done. I have met plenty of bright people who
> have trouble including NULL in their conceptual model of the database. They
> have a natural tendency to focus on the data and to assume the dbms just
> takes care of the missing information for them.

I don't consider them very bright if they won't educate themselves. We've put a great deal of effort into educating people about NULLs and 3VL. At www.firstsql.com, you will find 3 major articles about the topic as well as subsidiary information.

I'm kinda surprised that you are recommending that people not educate themselves and just follow their natural tendencies.

I feel like I'm fighting strawmen here. I'm defending RM's treatment of NULLs (and recommending that SQL's flaws in this area be fixed) against what? I'm not aware of any full-formed alternative for dealing with missing information (except SQL's).

I readily admit that I have not studied TTM, so I don't know D&D's current take on missing information. The last I heard they were still clinging to the Default Values solution, which is bashed on www.firstsql.com.

-- 
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)