Bridge Table
3 posters
Page 1 of 1
Bridge Table
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
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
Re: Bridge Table
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.
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.
Re: Bridge Table
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.
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
![-](https://2img.net/i/empty.gif)
» How to resolve a many to many relationship? Bridge Table or Utilize the same fact table?
» separate fact table/different grain - do I need a bridge table
» Data in a fact or dimenzion table or bridge table
» Bridge tables versus massive junk dimensions
» Multiple fiscal calendars
» separate fact table/different grain - do I need a bridge table
» Data in a fact or dimenzion table or bridge table
» Bridge tables versus massive junk dimensions
» Multiple fiscal calendars
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum