Morning "dice_in_trouble",
If I understand you correctly, you have or wish to have the following:
CLIENT TABLE:
CLIENT
DATE
BALANCE
ADDRESS
COLLECTIONS TABLE:
TIMESTAMP
PAYMENT
CLIENT
BALANCE
And if you update the client table you want the client and/or balance
updated on the collections table, and if you update the collections
table, you wish for the client and/or balance on the client table to be
updated.
I see a problem, you could end up in a circular loop where an update to
one table causes an update to the other which updates the first which
updates the second which ..
I think your design, as I understand it, is wrong. In a relational
database you should not be duplicating information, this is what
normalisation does for you, it helps you design your tables correctly.
Data should be updated once, in one place and that update will be
visible everywhere.
Off the top of my head, I would suggest something like the following is
needed:
CLIENT TABLE:
CLIENT_ID
CLIENT_NAME
ADDRESS
...
BALANCE TABLE:
CLIENT_ID
DATE?
BALANCE
COLLECTIONS TABLE:
TIMESTAMP
PAYMENT
CLIENT_ID
CLIENT_ID in BALANCE and COLLECTION will be a foreign key referencing
the client_id in the CLIENT table. Also, CLIENT_ID in the COLLECTIONS
TABLE will be a foreign key referencing the BALANCE table.
I'm not sure why you would need a DATE field (which is most likely a
reserved word, but you can check that for me!) in the CLIENT table. The
balance for each client, if your processing is correct, is "as of right
now".
So, you can now update the client details in one place, the CLIENT
table. Payments update the COLLECTION table, and, if necessary, a
trigger could then add the payment into the BALANCE for that CLIENT_ID.
However, if a payment by cheque, for example, needs 4 days to clear, you
may have to do some batch processing to update cheques that cleared into
the BALANCE table. It depends on your needs.
Maybe you would have a PAYMENT_CLEARED flag in the COLLECTIONS table,
and when that was updated to show that the payment had cleared, then a
trigger would update the BALANCE table for that client.
You might have a PENDING_PAYMENTS_BALANCE on the BALANCE table to total
up the payments that have been made, but have not yet been cleared for
that particular client. It all depends on what your requirements are.
HTH
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767
