Bridge Table

View previous topic View next topic Go down

Bridge Table

Post  ozisamur on Sat Feb 14, 2015 10:25 am

Think about I have customer dimension with 10 records.
And also hobby table it holds 5 record.
And just 5 customer have hobbies ( more than 1 hobbies so I should use bridge table ).

I should put those customer-hobby matches into the bridge table. So far everything ok.
Should I put the customer which has not any hobby into the bridge table ? For example in the below example customer 4 and customer 5 has not any hobby so should I put that into the bridge table or should I put just customer which has hobbies. Thanks

CUSTOMER_SK | HOBBY_SK
1 | 3
1 | 4
2 | 5
2 | 3
2 | 4
4 | -1
5 | -1

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Bridge Table

Post  ngalemmo on Sat Feb 14, 2015 3:19 pm

Including the no-hobby people in the bridge gives you more flexibility. Rather than use a dummy foreign key, you should have a no-hobby row in the hobby dimension and reference that.

If customer is a very large dimension, you may want to consider hobby groups. A group would be a unique combination of hobbies. The fact would contain the group key and the bridge would be between the group and hobby. Loading it is more work, but the bridge table can be significantly smaller.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bridge Table

Post  hang on Sun Feb 15, 2015 7:35 pm

You may not need to include no-hobby customer in the bridge at all, which would make COUNT/EXISTS based queries more straightforward.

With hobby group idea suggested by Ngalemmo, the fact table would be leveraged to track customer hobby change without growing SCD2 customer dimension alarmingly. If you are only interested in current customer hobbies or dealing with small customer base, you may just include the group key in the customer dimension, so that you don't have to go through much bigger fact table for just customer-hobby related queries.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Bridge Table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum