product types with different attributes

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?

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.

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?

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.

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