Deal with Bridge table

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

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?

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.