Deal with Bridge table

View previous topic View next topic Go down

Deal with Bridge table

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

Hi,

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.



D_CONTACT ----->F_PERMIT<---- D_ADDR




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



Thanks,
Bobby

bobby2929

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?

nick_white

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

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

View user profile http://aginity.com

Back to top Go down

Re: Deal with 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