Thanks. The actual columns do have good names; it was simpler to type
them that way for the example.
I'm trying to be extra strict with all the DDL, because the previous
incarnation of our database was very loose, to say the least.

-----Original Message-----
From: Adam [mailto:s3057043@...]
Sent: Friday, January 21, 2005 3:55 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Correct join design



Don't join to tables that you do not need the information. All you
are doing is ensuring the optimiser will have a harder time choosing
the right plan, and making the database do more work.

Your concern is a good concern, you want to ensure what is known as
database consistency. But within your query is not the appropriate
place to do so. Both AccountData1 and AccountData2 have the 'fk'
field that should be defined as a foreign key to the Account table,
with cascading updates and probably cascading deletes. I am not sure
on the specifics that define AccountData1 and AccountData2 as having
a relationship, so if it is more complex than a foreign key, you can
use triggers on the respective tables to ensure changes to any of the
tables gives you correct behaviour.

What I would change about your design is I would rename "fk"
to "AccountID", which is a sensible naming convention for foreign
keys. The following query would make perfect sense to anyone, the
relationship is defined by the AccountID.

AccountData1 join AccountData2 on AccountData1.AccountID =
AccountData2.AccountID

you would gain no benefit from

AccountData1 join Account on AccountData1.AccountID = Account.ID join
AccountData2 on AccountData2.AccountID = Account.id

So the pedantic answer is name your foreign keys a name that reflects
the relationship they represent, and allow the database to do it in
the most efficient possible way. The performance will decrease
because it needs to do additional joins. Both methods are legal
syntax, but there is always a bad way of doing things :). The only
reason you would include the account table is if you wanted to use
Account.Name for example, or if you wanted to include where
Account.InUse='T' or something like that.




--- In firebird-support@yahoogroups.com, "Rick DeBay" <rdebay@r...>
wrote:

> Given this relationship
>
> AccountData1 --FK--> Account <--FK-- AccountData2
>
> And the requirement to retrieve data from AccountData1 and

AccountData2

> (nothing is required from Account), what is the correct join?
>
> AccountData1 join Account on AccountData1.fk = Account.id join
> AccountData2 on AccountData2.fk = Account.id
>
> AccountData1 join AccountData2 on AccountData1.fk = AccountData2.fk
>
> The second version saves a join by cutting out the join to Account,

as

> we don't need any data from it. But this leaves out the element

that

> defines their relationship, which may not be desirable from a
> maintenance/documentation point of view.
> What I'm looking for is not what works or what is legal syntax, but

what

> is the pedantic answer, and how or whether it will affect

performance.

>
> Rick DeBay
> Senior Software Developer
> RxStrategies.net

Yahoo! Groups Links