Compound key attributes

View previous topic View next topic Go down

Compound key attributes

Post  Aikibart on Mon Feb 15, 2010 4:45 am

Hi all

I am struggling with following design issue: in our organization, a number of attributes (such as account executive, flagship store) are defined by the combination of customer and brand, where brand is a rollup of a product.
Our sales fact table is at customer and product level.

How do I model this? My thinking is to have a dimensional table d_customers_by_brand with primary key customer_surrogatekey and brand_surrogatekey. Joining to the fact table would mean joining the fact table customer surrogate key to this table's customer surrogate key, and joining the fact table product surrogate key through the item master table (which has the link between product and brand) to this table's brand surrogate key.

What do you think about this design? Should I have 2 surrogate keys, or just one which combines customers and brands?

Thanks in advance for your time and input.
Regards,
Bart
avatar
Aikibart

Posts : 1
Join date : 2009-02-03
Location : Belgium

View user profile

Back to top Go down

Re: Compound key attributes

Post  ngalemmo on Wed Feb 17, 2010 1:47 pm

The relationship between account executive, customer, brand for example, is a busines state. One would assume there is business process surrounding how this state is established. The purpose of a fact table is to record business events (such as transactions) as well as states. So, this relationship is best modeled as a factless fact table containing the appropriate surrogate keys as well as effective dates and a current flag.
avatar
ngalemmo

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

View user profile http://aginity.com

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