Implementation Bridge Table

View previous topic View next topic Go down

Implementation Bridge Table

Post  ozisamur on Sun Nov 23, 2014 10:23 am

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?

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Implementation Bridge Table

Post  ngalemmo on Sun Nov 23, 2014 11:17 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Implementation Bridge Table

Post  ozisamur on Mon Nov 24, 2014 3:16 am

Thank you man.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Implementation Bridge Table

Post  nick_white on Mon Nov 24, 2014 3:40 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Implementation 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