Customer with Multiple Address types

Hi, in my model every customer can have several address types addressses (Billing address, a Shipping address, a Correspondence addres, etc) but can only have 1 address in each address type. Example:

John Doe | Billing | 101 Road, New York City, NY
John Doe | Shipping | 102 Road, New York City, NY
John Doe | Correspondence | 103 Road, New York City, NY
Jane Doe | Billing | 505 Road, New York City, NY
Other Doe| Billing | 777 Road, New York City, NY
Other Doe| Home | 633 Road, New York City, NY

I think the best way to model this would be having 2 different dimensions (customers and addresses) and have a sort of bridge table with customer_id | address_id | valid_from | valid_to.

Any other suggestions on how to model this example?

Thanks

Hello,

I think you dont need a bridge table here. There can be one Customer Dimension and Customer Address Dimension(SCD2).

This should be enough...

Thank you!!

It is not unusual to have an address dimension that is independent of a customer dimension. After all, an address is what it is, and if you are dealing with a large number of customers, you can same considerable amount of space storing an address once.

To deal with address roles, they are often handled as multiple dimension references in fact tables. After all, the role an address plays is tied to the event for which the address was provided. Any address role associated with a customer is usually a preference rather than how it is actually applied in a transaction.

It also helps if the full address comes in as part of the transaction source data.

I read the posts but I am still confused.

I am facing a similar situation:
Employees have 3 types of addresses i.e. Contact Address, Permanent Address and Vacations Address. What is the best way to handle it?

1. Fact table references the Employees dimension and the Address dimension also references the Employee dimension.

2. There is a many to many relationship between the fact table and Address dimension. Bridge Table should should be used.

3. Any other way?

regards,

Customer Fact Table
CustomerDimKey
CustBillingAddressKey
CustShippingAddressKey
CustCorrespondenceAddressKey
CustHomeAddressKey

Or
If you want to keep an unlimited number of addresses for a customer:
Customer Fact Table
CustomerDimKey
CustAddressKey
AddressTypeDimKey

rf001 wrote:I am facing a similar situation:
Employees have 3 types of addresses i.e. Contact Address, Permanent Address and Vacations Address. What is the best way to handle it?

After several discussion with other BI Architects and reading what was explained here, we decided that the best way on doing this would be what @ngalemmo said: "To deal with address roles, they are often handled as multiple dimension references in fact table". This basically means that you have to build your Employee dim without address and then have an Address dim, then in the fact do somethiung similar to what @Jeff Smith said: This is how your fact table might end up looking:

EmployeeKey -> FK to EmployeeKey in EmployeeDim Table
ContactAddressKey -> FK to AddressKey in AddressDim Table
PermanentAddressKey -> FK to AddressKey in AddressDim Table
VacationsAddress -> FK to AddressKey in AddressDim Table

Thanks all for your help

@rhaces, I have the following question..

If a single row in the fact table has the following columns:
EmployeeKey| ContactAddressKey| PermanentAddressKey| VacationsAddress.
100 | 5 | ?? | ??
100 | ??| 2| ??
100 | ??|??| 4

What should be in the places of ??...? should it be nulls?

Fact table should always have proper FK relationships. No nulls, no outer joins to dimensions. Create a row in the address dimension for unknown addresses (address line 1 = 'Unknown' or something like that) and have fks reference that row when the address is not known.

Instead of having 3 rows, you would have 1 row with:

100 | 5 | 2 | 4 |

Hi,

I use this opportunity to spin a little furter on the topic Customer and adress(es). Say you have your customer dim and adress dim and wanted to track changing relationsship between them (the customer moving and calls in to say he's moving).
Is it so that in these situations we should use factless fact tables to deal with this ?

What if we only had a customer dim including adress(es) (with effective dates and no adress dimension, could that ba considered a fact table as well ?

Regards
'little bit confused'

Fact tables and dimension tables are different and not interchangeable.

If you had Customer and address in the same dimension, you would either have to have columns for each type of address or have a row for each combination of address and customer. If your Customer and address dimension had 1 row for every combination of customer and address, how would you know which row to put on a call?

If you have a customer and address dimension with, say, 4 sets of address, your dimension table is going to be huge. And let's say you want to aggregate calls by the state where the customer lives, you would have to join your calls fact table with you Customer dimension - 2 of your largest tables.

I have an address dimension and a seperate geography dimension with Zip Code at it's lowest level. This Zip Code dimension lets me by pass joins to the customer table or even an address table to aggregate facts to the Zip code, city, state, county etc.

A lot of it depends on how the data is going to be used. If data gets rolled up to the state, then it makes sense to avoid having to go through a huge dimension table to get to state. But if data never or rarely gets aggregated to zip code or city, then making that join fast has no practical purpose.

My questions really boils down to:
Is it OK to have Customer and adress in same dimension with one row for each combination ? Tracking all history.
(I know it depends on......many factors)
What do we loose doing so ?

Ole

Either way is acceptable. As you said, it depends.

You lose performance. Your Customer table will probably be your largest dimension table. Adding in multiple addresses for each member will make it grow significantly. It will slow the load and slow queries. And it adds to the complexity of your largest dimension table.

Jeff Smith wrote:You lose performance. Your Customer table will probably be your largest dimension table. Adding in multiple addresses for each member will make it grow significantly. It will slow the load and slow queries. And it adds to the complexity of your largest dimension table.

Generally I would agree but, it does depend. If the industry is manufacturing, there may not be large number of customers, so the performance difference doesn't matter. Retail, on the other hand, usually separates customer and address.

"it does depend'

The design always depends. It depends on how the data is used. It depends on the industry. It depends on the number of rows. It depends on the database software. Something used 1,000 times a day might get designed differently than something used once a month. Something used by the CEO once a month might get designed differently than if it were used once a month by the guy fresh out of college. The capabilities of the reporting software are taken into account. They are all factors.

So when a person asks how they should design something, well it depends.