[AccessD] Same data, different FK

John Colby jwcolby at ColbyConsulting.com
Thu Apr 20 13:49:44 CDT 2006


Good points.   


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Thursday, April 20, 2006 1:51 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Same data, different FK

John,

  Six one half a dozen another type of thing...

  I use Style #2 because from my viewpoint, the transaction is related to
only one other thing.  With separate fields, it would be possible to fill
both fields (i.e.. Someone edits the table by hand) and you now have one
transaction related to two things.

  You also have additional overhead in maintaining extra indexes.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, April 20, 2006 11:57 AM
To: 'Access Developers discussion and problem solving'
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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list