Implementation Bridge Table

Think about ORDER_ITEMs fact. There are ORDERS and some of the customer has those orders.

CUSTOMER is a dimension.
One customer can have more than one hobby. So there should be a CUSTOMER_HOBBY bridge table.
And also HOBBY is another dimension which is linked to the CUSTOMER_HOBBY bridge table.

So far everything is ok. ORDER_ITEM fact there are Surrogate Keys for Customer, CUSTOMER_SK. Actually for one customer there might be 2, 3 or more customer because of the SCD Type 2.
How should I fill the CUSTOMER_HOBBY table?

If I use the CUSTOMER_SK in the CUSTOMER_HOBBY, I could not match the ORDER_ITEM and Bridge table. Because some of the customer have new SK in order_item fact but it didn't be reflected to the BRIDGE table (CUSTOMER_HOBBY). Should I use CUSTOMER natural key ( customer_id ) in the bridge table ? This can be much more complicated for end users. What do you suggest?

Bridge tables can be annoying when one side uses type 2 keys. You have a few options:

1. Keep the type 2 in the bridge and add new rows whenever you create a new type 2 customer row.

2. Add a type 1 key to the customer dimension and use both type 1 and type 2 in facts. This is simpler than #1.

3. Create a hobby group dimension for every unique combination of hobbies, put the group key in the fact and the bridge. Not a good choice if you expect hobbies to change.

Thank you man.

ozisamur wrote:This can be much more complicated for end users.

Just an additional thought: none of these solutions should be more complicated for end users as the complexity should be hidden by your BI tool and/or DB Views. I would never give access to the raw DB tables to anyone I would consider an "end user" i.e anyone who would be phased by DB complexity and is liable to incorrectly join tables if querying the raw DB tables