Re: Informal Survey #1 -- joins on foreign keys

From: Rob <rmpsfdbs_at_gmail.com>
Date: Mon, 3 Oct 2011 11:05:01 -0700 (PDT)
Message-ID: <7b791378-c4b1-4c4a-bf62-d1afc24796c2_at_d17g2000yqa.googlegroups.com>


On Oct 3, 5:27 am, Erwin <e.sm..._at_myonline.be> wrote:
> On 2 okt, 19:58, Rob <rmpsf..._at_gmail.com> wrote:
>
>
>
>
>
> > On Oct 2, 10:07 am, paul c <toledobythe..._at_gmail.com> wrote:
>
> > > On Oct 1, 2:07 pm, Rob <rmpsf..._at_gmail.com> wrote:
> > > ...
>
> > > > I've designed hundreds of databases. After contemplating it long and
> > > > hard, I seriously
> > > > doubt I ever used a foreignkey-foreign key join in the queries I
> > > > authored for use with
> > > > these databases. Not because it was disallowed, but because
> > > > it lacks meaning. ...
>
> > > There must be thousands of db's that have Shipments, Invoices and
> > > Receivables tables with a foreign key referencing a Customer table.
> > > Obviously there will be people who will want to join two or more of
> > > those tables to compare Shipment_Value to Invoice_Amount or
> > > Receivable_Amount.
>
> > > Of course there are probably thousands, maybe millions, of people who
> > > have designed hundreds of databases that used no such join.  And,
> > > probably thousands of people who have designed dozens of databases
> > > that used only such joins.
>
> > In my original post, I asked (first) for a gut reaction. Yours is a
> > gut
> > reaction and happens to coincide with my own. But gut reactions
> > can be misguided.
>
> > For example, those people who "will want to join two or more of
> > those tables to compare Shipment_Value to Invoice_Amount or
> > Receivable_Amount" will likely do so THROUGH the Customer
> > table so as to provide a Customer attribute that identifies the rows
> > of the
> > result. How many would do it without the Customer table?
>
> A (natural) JOIN is an associative operation.  To speak of joins as if
> they are "THROUGH a table" is to expose ones ignorance of the
> relational algebra, or it is to expose ones failure to properly
> distinguish between model (the JOIN itself) and implementation (the
> evaluation strategy as chosen by the engine that executes the query),
> or it exposes yet some other important piece of knowledge that is
> lacking in your brain.
>
> Take any set of 6NF relvars.  For example, CUST {CUST_ID} , CUST_DOB
> {CUST_ID, DOB} , CUST_SAL {CUST_ID, SAL} , with key CUST_ID in each
> and FK CUST_ID in the latter two.  Now write the query that gives me
> the customer ID of all the customers for which both date of birth and
> salary are known.  Hint : if you include my CUST relvar, then you've
> got a relvar too many.
>
In spite of my ignorance, my failures and/or my brain deficiencies, (or perhaps because of them), you understood EXACTLY what I meant by (the associative preposition) THROUGH. And, you came to the identical conclusion: When no "parent" attributes are required, the result could be obtained without access to the parent relation. The "Hint" just says that a clever person doesn't need an optimizer to avoid the additional join. But I believe my observation is basically correct, that most sql authors would not catch it. (I also doubt any optimizer would catch it, but that's another matter.)

However, if it were CUST(CUST_ID,CUST_NAME) where CUST_ID was some opaque value, one could write the query as you suggest without CUST, but then the result would not indicate which CUST has known date-of- birth and salary. The result would only indicate the existence of such CUSTs. And that existence inference would itself be based upon the existence of a foreign key reference, with no certainty that the referenced tuple actually exists. (Does anyone else see a problem with that? What if referential integrity were not declared? Would that not imply that a well-formed query on a correctly defined database COULD produce incorrect results?)

As for the rest of your reply, you do not address whether higher-level abstractions can map to this questionable construction. Received on Mon Oct 03 2011 - 20:05:01 CEST

Original text of this message