Re: Is there a way to link a single PK to two foreign keys?

From: Bernard Peek <bap_at_shrdlu.com>
Date: Sat, 4 Nov 2000 11:55:24 +0000
Message-ID: <XwN$GXAsk$A6EwhO_at_shrdlu.com>


In article <IEsL5.9167$a7.307729_at_news1.rdc1.il.home.com>, Seamus Murphy <seamusm_at_home.com> writes
> Obviously, I don't know what I'm doing.  I'm just trying to create
> a simple financial database for home using Access.  I'd like to
> hold my balance, account, and transaction info all in separate
> tables.  However, I can't figure out how to relate my three tables
> without the conflict of assigning a single PK to two foreign keys.
>  
> Every account will have many balances AND every account will have 
> many transactions.  Does anyone have any suggestions or a better
> solution?  Thanks in advance.

An account only has one balance at any given time, and the balance at any earlier time can be calculated by replaying the transactions up until that time.

You could just store the transactions and recalculate the balance every time you need to use it but that would be slow.

You could store the balance after adding each new transaction. You could store it in the account record or in the transaction record. The transaction record could hold previous-balance, transaction-value and final-balance values. So the current balance would be the final-balance of the last record to be added.

Bear in mind that when you calculate and store the balance you are actually keeping the same information in two places. You might like to add a housekeeping job that periodically goes through all of the transactions and makes sure that the total of the transactions matches the value recorded for the final-balance.


>cid:003701c042fa$4e20bf00$69da0218_at_moline1.il.home.com
>  
>  
> Seamus Murphy
> seamusm_at_home.com
>
>[ A MIME image / gif part was included here. ]

Please don't post binary attachments in any non-binary newsgroup.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk
Received on Sat Nov 04 2000 - 12:55:24 CET

Original text of this message