What are some structures and models to store many-to-many relational data between two fact tables in a data warehouse? Currently, I am using a mapping table which includes the primary keys from both tables, but I am wondering if there is a better approach?
My specific use case is relating invoices issued to payments received. I have an invoice fact table (each record designated with a unique InvoiceId) and a payment fact table (each record is designated with a unique PaymentId). My mapping table has columns for the InvoiceId and PaymentId as well as other useful information, but InvoiceId and PaymentId are not necessarily unique in their respective columns (because a payment can apply to multiple invoices and an invoice can be paid with multiple payments).
Thanks in advance for any advice and suggestions!
Best Answer
I just stumbled on your thread accidentally when searching for some stuff. I just read about this in the Kimball DWH ETL toolkit and I might share it with you. Your mapping table is what they refer to as a 'bridge table'. The extra element that they add is a 'group entity', which avoid a many-to-many join. You might want to check that out. See page 196 of that book.
I quote: 'To avoid a many-to-many join between the bridge table and the fact table, one must create a group entity related to the multivalued dimension. In the health care example, since the multivalued dimension is diagnosis, the group entity is diagnosisgroup.'
Herewith the layout. Maybe it is of value to you. I thought it was quite interesting, although I haven't tried it out yet. I have used the similar concept like the bridge/mapping table within the context of object oriented software design though.