RE: Useful Oracle books - C.J. Date theory vs. practicality
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 28 May 2004 12:17:16 -0500
Thanks for the feedback, Lex, nice to hear your view on this. I have a few
comments/questions inline, below (also trimming a bit so I don't exceed the
quoted-lines limit):
Dan Tow
650-858-1557
www.singingsql.com
Quoting Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx>:
<snip>
> It is important to understand that null might mean "We don't know this
> property of this entity." OR it might mean "This property does not *apply*
> to the entity."
>
> LDH> I dare to disagree here: nulls are only allowed to represent one fact:
> LDH> information is missing. nothing more, nothing less.
> LDH> see also rule 3 of Ted Codd's 12 rules.
DST: So, how do we handle Spouse_ID for a single person in a Persons table?
DST: OK, I'm really talking about *real SQL* here, as actually implemented by
DST: Oracle, just to be concrete. I wouldn't call a null Spouse_ID in such a
DST: case missing information; I'd call it an accurate reflection of the fact
DST: that this person *has* no Spouse_ID. Any non-null value (in Oracle, as
DST: actually implemented) for Spouse_ID being set aside to reflect "has no
DST: spouse" would seem to me to be much less intuitive and more likely to
DST: create future problems. Is it possible that our disagreement is based on
DST: the direction we're coming at the problem? - I am looking for a theory
DST: that applies well to SQL as actually implemented, and that is directly
DST: useful to developers and DBAs stuck with that implementation. I am also
DST: greedy enough to hope that the theory points where we ought to go in the
DST: future, with future implementations, but what most of us need is a way to
DST: cope with the current implementations. (This was precisely my point when
DST: I said I sympathized with the idea that Date occasionally neglected
DST: practicality!) Admittedly, a purist who is strictly interested in
DST: optimizing the *future* implementations might want to avoid the confusion
DST: of dealing with how to limp along with an imperfect current
DST: implementation, and I respect that approach, but that isn't the direction
DST: I'm coming from.
<snip>
> 1) Everywhere you have column that would currently be nullable, declare,
> for that column, some *special* value to handle "we don't know" or "does not
> apply" (or perhaps two values...)
>
> LDH> yep, the "default value" alternative approach.
> LDH> this can only be implemented on the concept of strong domains --
> LDH> but unfortunately domains have never been implemented by the vendors
> LDH> (at least not by Oracle)
DST: Yes, but what should we do in the meantime, lacking domains? Isn't using
DST: NULL best, then?
>
> but wait, sometimes there *is* no value that could never be real for a given
> column, so we'll have to add whole extra Boolean columns for each such
> no-longer-nullable column to handle specifying whether we really ought to be
> ignoring the value in that other column over there, and we build special
> logic
> into the application to handle refering to these special Boolean
> pseudo-null-indicating columns almost everywhere we refer to the
> no-longer-allowed-null columns,...
>
> LDH> why? can't you represent this by a special value too?
> LDH> (maybe I am missing your point here)
> LDH> anyway, additional logic should NOT be built into the applications,
> LDH> but rather in the methods that are associated with the domains.
DST: Yes, but what should we do in the meantime, lacking domains?
>
> but wait, what about the column that starts out seemingly safe for
> designating, say, "-1" as the special "does not apply" value, but ends up in
> some special case needing "-1" for a *real* value - WHOOPS, I guess we
> better revert to another value, or to the special Boolean
> pseudo-null-indicating columns, and we'll have to fix all that special logic
> that formerly did special handling on "-1",...
>
> LDH> sounds like a serious design mistake to me;
> LDH> don't get me started about serious RDBMS design mistakes :-)
DST: In the real world, with the current number of near-perfect database
DST: designers, can design mistakes be made rare enough that we needn't worry
DST: about what we'll do when we discover them?
<snip>
> Sure, nulls are confusing, and associated with all sorts of logic problems,
> but those problems are largely *inherent* in the underlying logical
> problem...
>
> LDH> nulls would be much less confusing if they were implemented PROPERLY
> LDH> however, the SQL language certainly does not help here...
DST: Yes, but what should we do in the meantime, lacking domains?
>
> Imagine that you want data from bar-code readers at a supermarket to go
> straight into a database.
>
> LDH> the "famous" cat food example ;-)
> LDH> I think this is already the wrong way to start;
> LDH> why insist that this data should go straight into the database?
> LDH> this looks like a transaction design mistake to me.
> LDH> what if the customer wants to cancel (forgot his wallet)?
DST: What's wrong with
DST:
DST: ROLLBACK;
DST:
DST: ? Seriously, though, for performance reasons, I agree with clumping all
DST: the inserts until they're ready to happen all at once, at the end of the
DST: transaction, but that still doesn't mean it's necessarily worth the
DST: coding hassle to presort and pre-aggregate (now at the application layer)
DST: to avoid duplicates, when duplicates are rare enough that the extra rows
DST: are virtually cost-free, and the rows are never read except as
DST: aggregations. I run into a similar problem when I snapshot performance
DST: data into my own tables, with simple SQL and shell scripts - the data
DST: rarely produces a duplicate row (which really means that the snapshot saw
DST: the something twice, placing double weight on the event), and it'd be a
DST: hassle (and for what benefit?!) to eliminate the duplicates with such
DST: simple scripting tools, unless I added a purely artificial, useless
DST: Data_Row_ID column, where the data are only ever queried in aggregate.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
