Please may I have some advice/opinions on the best process to use in the following situation.

To deal with subscription payments in a small charity I use a cash matching system comprising

Table invoice PK invoice_id;

Table cash_payment PK cash_payment_id;

and linking Table payment_allocation PK allocation_id, FK invoice_id, cash_payment_id.

This gives me a many to many relationship between invoice and payment. (the invoice table is only used for admin, no real invoices are sent out)

All is good except for the following situation.

Some members are honorary and do not have to pay subscriptions. Hence there is no requirement to create a record for them in the invoice table. If they don't pay then all is OK.

However, some of these members choose to pay the subscription anyway, resulting in a record in the 'cash_payment' table that does not have a corresponding invoice record or one in Table 'payment_allocation' (as there is no invoice to allocate it to) .

I therefore have two choices. Either

  1. Generate an invoice for everyone, including those for whom the amount will be zero and record all payments made, linking them to the appropriate invoice. This will result simply in an over payment being recorded for those honorary members who choose to pay unnecessarily.

  2. Do not generate an invoice for any zero amount. When an unnecessary subscription payment happens I insert a record in Table cash_payment with a new payment_type of contribution but without any corresponding record in the payment_allocation table, This will avoid zero amount invoices but might make it more difficult to report on cash flow and things like over/ under payment.

I'm tending towards choice (1) Does anyone have any hints regarding which might be the better method?


1 Answer 1


In the end I went with option 1. That way it copes with people paying unnecessarily as there is an invoice against which to place the payment. It also allows for refunds to be made against zero amount invoices if someone pays unnecessarily but then wants a refund. The payment / refund can be put in the cash_payment table and allocated to the invoice through the payment_allocation table just like any other invoice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.