Customer & Multiple Addresses
3 posters
Page 1 of 1
Customer & Multiple Addresses
I have to model below in star schema. A fact has 1 customer . But the customer can have multiple addresses at any point in time.
All the addresses can be active at the same time. However the fact can not have customer address as a field. Thus the relationship is customer has multiple addresses. How can i solve this in dmensional model?
All the addresses can be active at the same time. However the fact can not have customer address as a field. Thus the relationship is customer has multiple addresses. How can i solve this in dmensional model?
neotechnical- Posts : 3
Join date : 2010-07-13
Re: Customer & Multiple Addresses
My guess is there are probably roles involved with the customer-address relationship as well...
The customer and address should exist as separate dimensions. Facts, such as orders, should have FK's pointing to the customer and applicable addresses (ship-to, bill-to, etc...).
For reporting customer and address information indepedant of any transactional activity, there should also be a factless fact table associating customer, the role if applicable (shipping, billing, payee, etc...), and the address over time.
The customer and address should exist as separate dimensions. Facts, such as orders, should have FK's pointing to the customer and applicable addresses (ship-to, bill-to, etc...).
For reporting customer and address information indepedant of any transactional activity, there should also be a factless fact table associating customer, the role if applicable (shipping, billing, payee, etc...), and the address over time.
Re: Customer & Multiple Addresses
Thanks for the reply. However the case is no role is assigned to address. The source system has customer and multiple addresses for a customer. So i cannot really put FKs in my fact table saying its a mailing address. The requirement is just to store multiple addresses of a customer.
neotechnical- Posts : 3
Join date : 2010-07-13
Re: Customer & Multiple Addresses
Existence of a role doesn't matter... I was assuming you were pulling from an ERP system which typically has separate customer and address tables and a role based associative entity between them.
Without a role you implement it the same way. You need a factless fact associating customers with addresses and effective dates.
Without a role you implement it the same way. You need a factless fact associating customers with addresses and effective dates.
Re: Customer & Multiple Addresses
How many addresses, 1,2, N?
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Customer & Multiple Addresses
max 5 .. I guess factless fact table is the way to go.. I am planning below
Dimensions : Customer , Address
Fact : Cust_Addresses
Dimensions : Customer , Address
Fact : Cust_Addresses
neotechnical- Posts : 3
Join date : 2010-07-13
Re: Customer & Multiple Addresses
If there is a hard maximum number you could model 5 relationships as well.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Customer with Multiple Address types
» Customer dimension with multiple addresses
» Customer and Addresses
» Addresses in Customer Dimension
» Customer addresses in a high volume retail environment
» Customer dimension with multiple addresses
» Customer and Addresses
» Addresses in Customer Dimension
» Customer addresses in a high volume retail environment
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum