Need a database design that is going to allow the following to happen… I have been racking my brain these last few days on an efficient and easy method to do this.
Need to allow a payment;
in full for one invoice,
part pay one invoice, and balance remaining (to be paid later),
to cover multiple invoices and possible part payment one or more of these invoices (ie payment on monthly account for example).
Also needs to be able to reverse the payment (so I need to keep track of payments/amounts to invoice #)
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.
Awesome responses guys! Will use what you gave and post here and apologies on the delay, was driving long distance to family for xmas but computer is now setup again
