Drug Dispense and Payment

I have two related tables one is
1. Drug Dispense ( Dispense id [pk], tran_no, Patient id ,tran date, doctor id, store id, drug id, cost, quantity and patient_amount_pays.......)
And another is
2. Payment ( Dispense id [FK] ,plan_type_id ,insurance_plan_no, insurance_amount_pays .....)

Dispense and Payment has “one to many” relationships. For one dispense there are more than one insurance plan paid the cost amount.
Cost amount = patient_amount_pays + 1 insurance_amount_pays + 2 insurance_amount_pays ……
Any thoughts on how to model this situation? Appreciate the help!!!
Kind Regards,
Rohit

Two facts. The payment fact should have the dimensions that the dispense fact has in addition to those unique to a payment.

Thank you Ngalemmo.
But if one dispense has at max four payment record than what is your suggestion?
And one more doubts can we make the relation between two facts (like in my case Dispense and Payment)?

My approach towards this design would be to not include the dispense key in the payment fact but instead add a fact less fact that holds a mapping between the payment key and the dispense key. However I can't help wonder why it would not be a good idea to hold the dispense key in the payment table itself or alternatively set up the payment measures within the dispense fact itself (as you mention, there can only be 4 payment records for each dispense record), apart from some complications in loading/processing data. Would love to hear other's opinion on this.

I would say, you need to define the grain at which you need to report the data. If it is at the dispense id, then you already have one fact table with dispense_id as the key and you can define the measures at the dispense level like patient payment, total_insurance_payment, total_cost. Here the total_insurance_payment needs to be calculated at the ETL level with the sum of all the insurance payments. Then total_cost is your patient_payment+total_insurance_payment. Then keep all your individual payments as a part of separate payment_fact table. The user can drill through from summary to the details if they want.
Another way is your can still flatten with individual insurance payments like insurance_plan1, insurance_amt1, insurance_plan2, insurance_amt2. In this option, you store both the summary and individual measures in the same table. But this sacrifies the flexibilty.

Dispensing and payment can be considered as two separate business processes. IMO they should be modelled acorrdingly, i.e with two separate fact tables. For reporting convenience you might build a view / cube which includes both these facts.

rohitrichhariya wrote:

Thank you Ngalemmo.
But if one dispense has at max four payment record than what is your suggestion?
And one more doubts can we make the relation between two facts (like in my case Dispense and Payment)?

If pharmacy is like any other retail environment, what was sold (dispense) has no direct relation to payment information, other than the payment was for the things in the sale.

I go in, pick up my prescription with two items and pay for it. Dispense will tell you what was sold and for how much (what I was charged, what was charged to insurance, and other stuff (cost, etc...). Payment will tell me where the money is coming from... cash, debit card, credit card, etc.. And there may certainly be a combination of things. I don't really care how many there will be, only that there can be more than one. The only relationship is the totals from both should tie.

A dispense should have a unique ID associated with it. A lot of times it is a combination of date, store, register, and transaction number (a sequential number generated by the register). That would be stored as a degenerate dimension value on both the dispense and payment facts (or a collection of dimensions). It may be just the register and the transaction number actually need to be degenerate dimensions, since date and store should be dimensions on their own right. This reduces the space required.

As with any pair of fact tables, you perform aggregate queries on both facts individually, summarizing the data to the same set of dimensions (or dimension attributes) then combine the summarized results along those common dimensions or attributes (you can do a join or union).