Deal with Bridge table

Go down

Deal with Bridge table

Post  bobby2929 on Mon Dec 15, 2014 6:30 pm


I have S_CONTACT---->S_CON_ADDR<--------S_ADDR

S_CONTACT----> Permit_item<--------S_ADDR

-----> is 1 to many relationship

My final model i am planning as below.


My Question is how should i deal with S_CON_ADDR in final model



Posts : 4
Join date : 2014-12-15

View user profile

Back to top Go down

Re: Deal with Bridge table

Post  nick_white on Tue Dec 16, 2014 3:54 am

Hi - it depends on what your reporting requirements are and what the intersection table actually represents.
If you can only have one address active at any one time then effectively you have a 1:1 relationship
If you have different types of address (home, work, shipping, billing, etc.) but can only have one address of each type effective at a time then you have many 1:1 relationships

It also depends whether you are interested in location information or address information (or both), by which I mean:
Location - attributes that can be used in analytics e.g. City, state, country, etc. You could have many customers with the same location information. You would use this information to analyse your customer base by geographical attributes
Address - a postal address including building name, premise/street etc. You would normally only have one customer per address. You might use this information for generating mailing lists from your DW

I normally treat location data as a separate Dim whereas I tend to denormalise address information into the Customer Dim - so the Customer Dim could include separate sets of fields for billing and shipping addresses.

Hope this helps?


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

View user profile

Back to top Go down

Re: Deal with Bridge table

Post  ngalemmo on Tue Dec 16, 2014 6:49 pm

To review your model:

1. A contact has one and only one contact address.
2. A contact has multiple permit items and each permit item has an address.

With the fact at the permit grain, there can be multiple permit item addresses depending on the number of permit items.

You have 3 choices:

1. Increase the grain of the fact to the permit item level.  Each row would have one contact address key and one permit item address key.

2. Keep the grain the same and add a bridge with permit key, permit item and address key.

3. Have two fact tables, one at the permit level without permit item address info, and another at the permit item level with a permit item address.

I would go with 1, presuming there are no measures at the permit level.  It keeps the model clean and allows additional analysis, such as the number of items in a permit without the complexity of a bridge.

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

View user profile

Back to top Go down

Re: Deal with Bridge table

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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