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