[AccessD] Same data, different FK

John,

The second solution is more flexible:

> IDFK                 <Place FK from Claimant and Payee in this field
> IDFKType         <Place an indicator of which type of FK this is.

>From relational data model purists point of view yet another solution with 
additional relationship tables is more correct and as flexible as the second 
one.

Shamil

----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Thursday, April 20, 2006 8:56 PM
Subject: [AccessD] Same data, different FK


> This is a situation I have run into before and just wondered how you guys
> handle this.  The situation in this particular case is:
>
>
>
> A claimant can have direct deposit.  The direct deposit can go into 
> several
> different accounts.
>
> A "Payee" can have direct deposit.  The direct deposit can go into several
> different accounts.
>
>
>
> So you have common information:
>
>
>
> tblEFTData
>
>
>
> FKID
>
> BankName
>
> RoutingNo
>
> AcctNo
>
> IDCLMT            <FK from the Claimant table
>
> IDPAYEE          <FK from the payee table
>
>
>
> Do you create two fields to hold the possible FKs?  This makes clean SQL
> implementation - you can join the Claimant table or the Payee Table, just
> join on the correct field.
>
>
>
> Or.
>
>
>
> Do you use a common FK field and a "FK Type" field
>
>
>
> IDFK                 <Place FK from Claimant and Payee in this field
>
> IDFKType         <Place an indicator of which type of FK this is.
>
>
>
> Again, this can be done fairly simply in SQL, join on the common field, 
> and
> just feed in a different IDFKType in the Where clause?
>
>
>
> The second method does not allow simple engine enforcement of relational
> integrity, but it allows easy creation of other entities using tblEFTData.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com