Structure for Payments, Accounts Receivable

[jtice]

I have an Client | Estimate | Job | Invoice solution that I'm redesigning from scratch. I now need to incorporate the functionality to record payments. Ordinarily each payment is for one invoice paid in full. But when I receive an advance or partial payment, or if the amount of payment for some reason is not identical to the invoice amount things get more complicated and I want the flexibility to handle this gracefully. The Invoice structure is simply an Invoice table with a child for the line items. I don't really want to separate payments from the individual invoices––iow, I want to be able to credit a payment to a specific invoice––but I also want to have the ability to credit payment against the overall account when appropriate because of exceptions. I'm sure there must be a tried and true structure that works well, so I'm asking for the best practice for structuring the table and relationships, whether to export (or script new record creation) to a separate payments table, do it within the invoices table, create a second child of Invoices to record payments and relate them to invoices... how is this typically handled in robust solutions?

Thanks!

[comment]

See this recent discussion:

http://fmforums.com/forum/topic/76398-allocating-payments-to-invoices-questions/

[jtice]

See this recent discussion:

http://fmforums.com/forum/topic/76398-allocating-payments-to-invoices-questions/

Thanks- yes, I saw that thread before I posted. I was looking for something more conceptual- data modeling. I found these resources that might be helpful to others. The first link, if you back out to the index, will show ERDs for a large number of database patterns.

http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm

http://www.amazon.com/exec/obidos/tg/detail/-/032111230X/qid=1125540378/sr=8-1/ref=pd_bbs_1/002-8156232-3043234?v=glance&s=books&n=507846

[jtice]

Since one invoice can be paid by many payments, and one payment can be applied to many invoices, it will require a join table to match them up. Working out the practicalities.