--- In [email protected], 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?
