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

<<And what is the purpose of a DBMS? (in this context) >>
In this precise context, the primary purpose of the DBMS to guarantee 2 things: correcteness and economy . First, by preserving data integrity the DBMS should guarantee the *correcteness* of results without relying on the application layer. In SQL, SELECT statements should be expressed only according to search criterias necessary not imposed by the fact that they must be here to guarantee correcteness. Second, a consequence of First, is a purpose of economy as additional resources should not be consumed in order to insure results correctness of result at run time. Resources should be consumed only to exclude tuples meeting specific criterias. As an illustrating example, if a poor design on a table creates a situation where each count view will be associated to an overhead of *correcting* (correcting in the sense that it allows to make count results right) conditions then the IO price is heavy but not only: there is no guarantee that the rules for setting up these conditions will be formalized which will lead in many cases to false results.

<< 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.>>

Unfortunately, and given the current trends, most *designers* are unfortunately poor designers because they neither educate themselves onto relational fundamental concepts and are certainly not supported enough by current DBMS implementation to do without that kind of knowledge. Do you know a lot of *designers* that can actually tell you why normalization is important?

<< 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.>> I understand the point you are making but you are not understanding mine. My point is not that DISTINCT is a trick. My point is that DISTINCT is a caution.

<< 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.>> Consider the following 2 queries:

select count(*) from table1 -->say it returns 5000 select count(*) from table1 where field1 = 'A' --> return 2000 then select count(*) from table1 where field1 <> 'A' should return --> 3000 right?

Well you will find out that results of
select count(*) from table1 where field1 <> 'A' may present supor

Correct results are results that do not need to be adjusted by additional overhead conditions such as IS NULL, IS NOT NULL at each run time...

<< Yes, but.... If you compare SQL with available alternatives, it
comes off
 looking well.>>
Because there are no serious alternatives does not mean it is *well*. At least clients that come to me to help solve their crappy DBMS problems do not consider and neither do I. I have clearly written that SQL has not yet met its match for expressing logic of extraction better but that certainly does not make it an acceptable relational implementation.

<<If you compare it with what ought to be built, it's defective, as
Codd
 pointed out.>>Yes no doubt.

<< SQL NULLS and three valued logic are separable. It isn't hard once
you get
 the hang of it.>>

<<real world limitations>>

<< 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.>> I understand and sympathize with your view but I do not accept. Whether it can or can not be done is the not the point. I know it can be done.
3VL handling of missing data is false because it makes predicate logic ineffective into guaranteeing integrity. Accepting 3VL for handling missing data draws to error sooner or later.  Whether you are personally aware of that does not guarantee other people in your company are.

It seems to me you underestimate the importance of the NULL problem. I suggest some reading on that. "Practical Issues in Data Management: A Reference for the thingking Practionner" from Fabian PASCAL has a complete section explaining that problem. Received on Fri Jun 02 2006 - 18:06:05 CEST