General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Database design - relating back to itself
I have Accounts - 1 rec per account
Account_Txns - a rec per financial transaction per account
I need to transfer monies between accounts.
Example:
Account 12345 needs to transfer 10.00 to account 98765
Account 23456 needs to transfer 20.00 to account 98765
Not only do I need to create an Account_Txn for account 12345 for -10.00 and for account 23456 for -20.00 but I need to end up with only 1 transaction on account 98765 for the total of +30.00
My problem seems to be that when I think about creating a view to show an account's txns, no matter what kind of design I come up with, I would need to be querying Account_Txns with a join back into itself for the other account's Account_Txns. I know, within reason, I should not be relating an Account_Txn to another Account_Txn but it seems to be the "nature of the beast" because I am transferring between accounts. I am in a vicious circle.
Can someone show me in a generic nature how this might be accomplished?
Thanks,
John
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only