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