--- In firebird-support@yahoogroups.com, Norman Dunbar <Norman@...> wrote:

>
> 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
>

Thanks Nrom for the reply. I also thought of this idea, wherein I make a separate table for the balance then link it up to the other 2 tables. But I don't know how to make table relations. I am using flamerobin to build up my firebird database. Can you give me a simple example of making a relationship between 3 tables?