I have the following structure:
dim_customer(cust_ck PK, …)
bridge_org_hierarchy(parent_cust_ck PK, child_cust_ck PK, …)
fact_some_facts(cust_ck PK, date_ck PK,…)
I can connect dim_customer to bridge_org_hierarchy using cust_ck = parent_cust_ck and it will pop up as a one to many just fine. I run into issues when I try to connect bridge_org_hierarchy to fact_some_facts using child_cust = cust_ck. I want the relationship to show as a M:N but Toad insist upon forcing me to use keys that I don’t want to and showing it as a 1:M.
If you were doing a logical data model where there are no such things as PK’s then you could do many to many relationships. You cannot model many to many in physical because it cannot be properly implemented by any relational database. many to to many relationships should be resolved by creating intersection or bridging entities/tables. Keeping them and then trying to handle in application code is just an exercise in futility - most often resulting in bad data.
" many to to many relationships should be resolved by creating intersection or bridging entities/tables"
Yeah that’s what bridge_org_hierarchy is. But unlike other bridge tables one side is a many to many. In this case the relationship to the fact table is 1:M from the dimension THROUGH the bridge table. Here is some sample code of what that looks like:
SELECT *
FROM dim_customer d
JOIN bridge_org_hierarchy b
ON d.cust_ck = b.parent_cust_ck
JOIN fact_some_facts f
ON b.child_cust_ck = f.cust_ck
AND d.customer_name = ‘Some Customer’
JOIN
ON
GROUP BY d.customer_name
Can you email me offline your three table’s DDL so I can see better what you’re trying to do? If so - email me at bert.scalzo@quest.com.
Never mind. Thanks for the input. Turns out that what I was doing was overkill.