product types with different attributes

View previous topic View next topic Go down

product types with different attributes

Post  salaman on Sat Jan 04, 2014 7:25 pm

Hi,

Say I have a product dimension for a store that sells many items ranging from electrical goods to foods.

90 percent of the attributes in the product dimension are common across all product types. But for food type products some additional attributes exist (e.g calories, fat content, sugars, etc...)

I could put those attributes in the core product dimension but for non-food product types those attributes would have to be marked as "non applicable".

Alternatively, I've thought of creating a specific food dimension that contains the unique attributes to food products and have 2 foreign keys in the fact table. In this instance, where a product is non-food type I'd have to link back to the food dimension on a "non-applicable" key.

Are either of these options advisable or is this scenario best covered by heterogeneous products? I've gone through the section in Kimball's book and must admit that I don't full grasp it.

How about maintaining a core sales fact schema for all products and then creating a new schema specifically to deal with food sales, where I could then link to the relevant food dimension?


salaman

Posts : 21
Join date : 2011-03-24

View user profile

Back to top Go down

Re: product types with different attributes

Post  ngalemmo on Sun Jan 05, 2014 12:29 am

You could put the food specific attributes in a different table, but use the same key as the product table. This would allow you to use the same fact FK to get the food attributes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: product types with different attributes

Post  salaman on Sun Jan 05, 2014 3:16 am

Thanks for your reply...

Does this mean that a single FK in the fact table would join on two dimension tables (the product and food dimensions)? Or is the food dimension a subtype of the product dimension - and is this not a type of snow flaking?

salaman

Posts : 21
Join date : 2011-03-24

View user profile

Back to top Go down

Re: product types with different attributes

Post  ngalemmo on Mon Jan 06, 2014 12:53 am

The food dimension is a subtype of product, but it is not snowflaking. Snowflaking is when you have a FK on a dimension that references another dimension. In this case, the food dimension row would have the same PK as the corresponding product dimension row, allowing you to access it directly from the fact's product FK.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: product types with different attributes

Post  salaman on Mon Jan 06, 2014 3:46 am

I see - so there is no physical join between the fact and the subtype dimension but there is, in effect, an implied join.


salaman

Posts : 21
Join date : 2011-03-24

View user profile

Back to top Go down

Re: product types with different attributes

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