Customer & Multiple Addresses

View previous topic View next topic Go down

Customer & Multiple Addresses

Post  neotechnical on Tue Jul 13, 2010 9:13 am

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?

neotechnical

Posts : 3
Join date : 2010-07-13

View user profile

Back to top Go down

Re: Customer & Multiple Addresses

Post  ngalemmo on Tue Jul 13, 2010 1:51 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Customer & Multiple Addresses

Post  neotechnical on Wed Jul 14, 2010 5:01 am

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

View user profile

Back to top Go down

Re: Customer & Multiple Addresses

Post  ngalemmo on Wed Jul 14, 2010 3:23 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Customer & Multiple Addresses

Post  BoxesAndLines on Wed Jul 14, 2010 8:31 pm

How many addresses, 1,2, N?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Customer & Multiple Addresses

Post  neotechnical on Fri Jul 16, 2010 4:58 am

max 5 .. I guess factless fact table is the way to go.. I am planning below

Dimensions : Customer , Address
Fact : Cust_Addresses

neotechnical

Posts : 3
Join date : 2010-07-13

View user profile

Back to top Go down

Re: Customer & Multiple Addresses

Post  BoxesAndLines on Fri Jul 16, 2010 9:23 am

If there is a hard maximum number you could model 5 relationships as well.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Customer & Multiple Addresses

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