Self Referencing Relationship for FACT table ?

Hi ,

We have a scenario where a shipment has an invoice associated with it. This is the 'Original Invoice Number' assoc with a shipment. Now, whenever there is a change in the price of the shipment, a new invoice number is being generated.
I wish to capture the new invoice number for the same shipment, but also map back to the original invoice number in a FACT table.

Is it a good practice to have a self referencing connector for a FACT table ?

Thanks,
Karan

You invoice shipments, you don't ship invoices. Invoice facts should reference the shipment, not the other way around. It would solve the problem you describe as it no longer matters how many times an invoice is adjusted, as they only apply to one shipment.

karan_das wrote:Hi ,
Is it a good practice to have a self referencing connector for a FACT table ?

Generally, it is a bad idea. Self joining a huge fact table might decrease the query performance by factors. Of course, there are exceptions ..