A payment is allocated, not assigned

The natural first model is a payment with a foreign key to an invoice. It survives until the first customer pays a round number that covers two invoices and part of a third.

What actually happens in collections: money arrives, and someone decides where it goes. Across instalments in order. Across several invoices. Partially, leaving a remainder that becomes credit. Sometimes reallocated later, because the customer says it was meant for something else.

None of that fits a single foreign key.

The model that holds

A payment is its own entity, with an amount and a date. And separately there are allocation records linking it to what it settles — each with its own amount. The sum of allocations is at most the payment. What’s left over is credit on the account.

What this buys, beyond correctness:

• Reversal becomes possible. Undoing a payment means undoing its allocations — which are explicit records, not a mutation you have to reconstruct.
• Partial payments stop being a special case.
• The question “why is this instalment settled?” has an answer you can show a customer.
• Reallocation is a normal operation, not a data fix.

The cost, and why it’s worth it

Every read is a join and the balance is a sum. That’s the price — and it’s the right one, because the alternative isn’t simpler: it’s the same complexity implemented as a series of exceptions.

The general lesson: when a relationship between two business objects has a quantity, a date or a reason attached, it’s not a foreign key. It’s an entity.