Jim Dettman
jimdettman at earthlink.net
Thu Apr 20 12:50:57 CDT 2006
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