Hi,

I couldn't find a section for design questions like this, so I've placed it here in chatter.

I am working on a system where sales are entered, then when payments are received a receipt gets printed. The receipt and the invoice both show the balance due or outstanding.

The tables I have are Sales and Sale Details and Payments and Payment Details.

The Sales table has a field for Balance Due and the Payment Details table (related to the Sales table by Sale ID) also has the balance due stored in it.

I have two problems with this design:

1. Editing Payments causes problems with the balance due. For example, I make a sale for $600, then a payment of $400 is made against this sale. The Balance due on the Sale is put to $200 and the Balance Due on the Payments details record for this sale is put at $200. If another payment gets made of say $100, then the Sale's balance due is set to $100 and the payment details is set to $100. If I realise that a mistake was made on the first payment and it was actually $250 that should have been entered, changing the $200 payment to $250 causes all my balances to go off.

Should I not let a payment be changed? Only let them add new payments?

Or would a re-design help? Any suggestions?

Or is the design good enough and it is just that I need to change the way my calculations are done when calculating the balance due?

2. I dislike having calculated fields stored in the tables, I would much prefer to calculate the balance due on the fly. However doing it this way speeds up reporting and the user interface, so I will live with this.

Thanks for any opinions.