Basic structure concerns

[LaRetta]

I have a Services db. This contains therapeutic services performed on clients (ServiceDate, Client ID, TherapistID, etc.). I have a Clients db, a Therapist db, and a Procedures db (containing ProcedureCode and Rate). I have a Contracts db which contains agreements from several

[kennedy]

I think you're about there... you need an Invoices db (you called it LineItems?) that is keyed by Service ID and Payor ID, has Date Billed and Amount Billed. Now if everyone would just pay their darn bills, then you could add Date Received and Amount Received right there. But that's not too realistic.

What you have to decide from here is how you want to handle multiple payments on an account (tied to account, or tied to invoice?). And how you want to handle repeated sends of an invoice (showing full account balance, or just showing one service and payments for that specific service). Since, realistically, some customers will end up paying multiple invoices with one check, usually its best to go to an account model...

So, the PayorID keys an account made up of dated invoices and dated payments and dated statements. That's three db's keyed by Payor, the first also keyed by Service.

Well, that's one answer... I'm sure there are others. HTH.

[LaRetta]

That

[LiveOak]

The general answer is that the world is complex. You probably need a join file between payments and invoices, since one payment can be applied to multiple invoices and one invoice can be paid in multiple payments. You may need another join file between payments and programs, if a payment/invoice join transaction can't ALWYAYS be totally allocated to a single program.

-bd

[LaRetta]

You nailed my problem, Brent! I thought I was complicating the issue -- was it necessary to have so MANY joined db's? Okay, I'm going for it! I will document this structure carefully, as the relationships could get complicated. I think it could get complicated 're-loading' an invoice for billing to a different Payor, but that's another issue (and probably another post) once I get this structure set up. I also still need to identify how accounting needs this re-bill situation attached to account codes. Thanks much to both of you for helping me.

[LaRetta]

I have it listed on paper and it makes sense, except for one piece ... adjustments. Should payments and adjustments be in the same db? If the adjustment is an overpayment by a Payor, that would work because it would adjust an Invoice payment and reflect in Program. If the adjustment is an error in Service entry, I can't correct the Service after it's posted - it must be a new entry. To correctly display services provided, it MUST be the Service db, right? Management likes to see exact Procedures performed, so we'd need to 'back out' the procedure from Services. That would generate a 'correction' Invoice (which wouldn't bill) and Program would reflect correctly. Did I lose you all like I just lost myself?

Right now, corrections to Service are not corrected. They are 'written off' as a uncollectible payment (so to speak). So, in Service, I could 'back out' the Procedure by entering it again with a minus. That would back it out of everything. And, 'billing' adjustments could be handled from the Payments module. Right? Am I missing anything here?

[LiveOak]

I favor a structure which puts payments, adjustments, credits, and write-offs in the same file.

-bd