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
