tracking payments - anyone with knowledge?

[scubadiver007]

I have

a table of indicators, some of which have a standard cost value attached to them.

a table of patient activity which records aggregated data for each indicator

a table that records these standard costs with a datestamp.


What I want to do is somehow match the patient activity records to the correct payment.

The more I think about it, the more complex I think it seems to get. I don't even know where to start or how to deal with it.

It is something that would be useful for the long term to track total costs.

... so I am open to ideas and questions I need to consider.

 

[DCrake]

This maybe one of those occasions whereby you store a normally calculated value into a field. This is becuase you need to know the cost at point of entry. As the costs can fluctuate and attempting to address the relevant cost historically may be dificult. Therefore on this occassion I would store the value in the table.

 

[scubadiver007]

That is what I am thinking.

The payments table would store the unit cost and then use a query to multiply the value by the number to get the total.

The most fundamental question is whether this is something the department even wants to be able to realistically achieve.

 

[gemma-the-husky]

i would use another table to store the allocation. the reason being that in general in any sales ledger/accounts receivable

a) one payment may pay several accounts
b) one account may be paid in several stages


so in simple terms

invoice table
payment table (to store the date/total/discount allowed etc)
payment allocation table (to store the payment/invoice cross refs with amounts)


I really think trying to avoid an allocation table is likely to give you a lot of pain long term!

Not sure that you HAVE to store anything else in the invoice table - although practically you would probably add a fully paid status indicator, and a payment to date field.

If you have repeat business then you probably need a ledger as well

Please note that I think there tends to be some redundancy in such ledger systems - ie most systems do store a ledger balance outstanding on each account, even though you can get this figure by adding invoices, and they store an invoice outstanding amount on each invoice - although you can get this by adding payments.

just traditional accounting praxis, i think.