[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