| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Useful Oracle books - C.J. Date theory vs. practicality
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_at_naturaljoin.nl>:
<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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri May 28 2004 - 12:14:16 CDT
 |
 |