I need help setting the tables and relationships for this scenario. Sorry, it's complicated!
I need to keep track of cases I do. The cases have patients, and each patient has a health fund. The health funds are grouped into health fund groups for the purposes of their rebates. I charge the patient a fee for a service, and the patient is variably rebated some or all of that fee by a mixture of government and health fund rebates.
The patient's fees are worked out according to 2 general types of schedules. The first type of schedule sets an amount in dollars for a certain service item (lets call it a fixed amount schedule - FAS). The second type is called a relative value schedule (RVS) and for each service item it sets a unit value (UV) (eg 6 units) and then an overall unit price that applies to every item (eg $10). Any case has a mixture of these two types of item.
The health fund groups have their own 2 schedules that determine how much they will rebate patients for the fees i charge. The government has its own 2 schedules. I set my fees according to my own 2 schedules. The schedules are all the same structurally but differ in either the unit price charged/rebated (for the RVS) or the dollar amount for each item (FAS).
The RVS table would have RVS_ID; RVS_ItemCode; RVS_ItemUV; RVS_ItemDescription
The FAS table would have FAS_ID; FAS_ItemCode; FAS_DollarAmount; FAS_ItemDescription
Associated with the RVS table there would be a unit price - indeed the RVS table is essentially identical for me, the funds etc, with the only difference being an external one (the unit price). The FAS schedules are also pretty similar except that each record in the different schedules would have a different dollar amount.
Every case would have a variable number of each type of item (RVS or FAS) and each item could of course be involved with any number of cases - so I'm thinking a many to many situation that would involve a linking table.
What I need to do with every case, and here's the complicated tricky part but i can't work out, is to select a number of these different type of items, select the patients health fund, select from a number of different billing options (eg my own schedule, or I might choose to bill according to the health funds own schedule so that the patient will be rebated exactly what he's been billed, or even just plonk in a total dollar amount regardless of the items), and have an idea of both my total fee, and also the amount the patient will be rebated so I can work out what gap payment (if any) they will need to make. So really I need to calculate two total amounts (one for me, one rebated) for every case.
The total fee would look something like this: (Total Units x Unit price) + Total Dollar Amount (to take into account the two types of item RVS and FAS involved)
I might stop there and see whether anyone can make any sense of what i've just typed! Thanks in advance