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

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?

As for the thousand, millions, hundreds, thousands and dozens of which you write, that is more gut feeling. Let's leave the monkeys-and-typewriters and statistical arguments behind. The semantics (as opposed to the mechanics) of foreignkey-foreignkey joins are not without questions.

What I'm really trying to get at is whether a foreignkey-foreignkey join makes sense algebraically, and if so, do operations in higherlevel

abstractions (like E-R, facts) translate to them? Received on Sun Oct 02 2011 - 19:58:16 CEST