I did that but quickly realised I needed some method to track the one payment across multiple invoices, and in some conditions, part payment of an invoice. We cannot just record a payment value and FK to invoice since we lose the ability to reverse the transaction should we decide to delete the payment…
I haven’t thought it through too much further yet but you should be able to have an amount column in the join table to track how much belongs to each invoice. I think this would allow for partial invoicing too. You may want a ‘status’ column on the invoice table to mark a payment as complete vs. incomplete.
To dniznick’s list, I would add Customer as an entity. The Customer will hold the account# (or just customer_id) that will allow you to assign ownership of your invoices. A customer’s outstanding balance might be multiple invoices. If a payment can cover more than one invoice and the individual invoice#s are not included with the payment, you’ll need some sort of rule (oldest bill’s first?) to apply the payments.
You might want to investigate breaking payments down into sub-payments when they apply to multiple invoices. This would make it possible to normalize the many-to-many relationship between payments and invoices.
Also, it’s probably better not to delete a payment. In traditional accounting, you’d add a reversing entry with a negative value.
A detailed design will flesh things out. When in doubt, start with a list of all the entities, followed by the relationships between them. Throw in primary and foreign keys. Make cross reference tables to get rid of many-to-many relationships. Review your list of requirements and adjust.