Re: The wisdom of the object mentors (Was: Searching OO Associations with RDBMS Persistence Models)

From: David Cressey <>
Date: Fri, 02 Jun 2006 12:05:55 GMT
Message-ID: <DQVfg.115$Eo3.18_at_trndny02>

"Cimode" <> wrote in message
> Thank you for your feedback...
> <<Bad duplicate treatment can be avoided by
> judicious use of primary keys and the "distinct" feature>>
> Absolutely. However these are turnaround solutions to insure the
> integrity of data that are to implemented in applicative DBMS code when
> they should be dealt with by the DBMS. As a result, during system life
> cycle what should be done once is often done each time a new view is
> defined which defeats the purpose of a DBMS.

And what is the purpose of a DBMS? (in this context)

> A practical problem
> arising happens for instance when a developper leaves a company. If
> this developper has participated in the design (or lack of design)
> process then this developper is probably aware of all the special
> conditions and features he should add (DISTINCT, IS NULL IS NOT NULL)
> to make count results correct.

That's poor design. When a poor designer leaves a company, a lot of the damage becomes apparent.
The problem arose when the company accepted the poor designer's work.

Proper use of DISTINCT is not some "clever, wierd trick" that only one developer knows. It's part of the knowledge shared by ALL competent database designers who appreciate the power and simplicity of the relational model and also, for one reason or another, are using SQL.

Perhaps you could tell me which results ARE correct? When working on databases I've designed, I rearely if ever need to "correct the count". It's already right, because it's counting things that are there.

> But as soon as this developper leaves
> the company his knowledge often get lost which in most cases will make
> the next generation of count queries return false results because the
> new developper will not be necessarily aware of the conditions required
> to make results correct. In a word the use of features such as
> distinct are already meaning a failure on a relational standpoint. In
> the case of DISTINCT, their suggested use was made by Codd as a warning
> about that limitation.

Yes, but.... If you compare SQL with available alternatives, it comes off looking well.

If you compare it with what ought to be built, it's defective, as Codd pointed out.

> <<NULLS are handled pretty well by SQL>>
> The problem is not whether NULL values are well handled by SQL, the
> problem is that NULL values are SQL's only way of representing missing
> information and that this way is a poor one because relational
> predicate logic suporting relational model does not support on three
> valued logic on which SQL NULLS are essentially based.

SQL NULLS and three valued logic are separable. It isn't hard once you get the hang of it.
I like two valued logic, myself, and shun three valued logic in SQL. OTOH, I recognize that, due to real world limitations, every now and then I have to deal with data that isn't there. It can be done. Received on Fri Jun 02 2006 - 14:05:55 CEST

Original text of this message