Conformed Fields or Snowflake? - Shop and Customer Location

[rendybjunior]

I have shop dimension and customer dimension which both has location information.

Should I create location dimension to correlate shop location and customer location?
I believe this one is called snowflake schema. I heard that it is hard to maintain.

Code:

    |----------|       |---------|       |----------|
    | Dim_Cust | ----- | Dim_Loc | ----- | Dim_Shop |
    |----------|       |---------|       |----------|

Or, should I maintain definition that shop and customer dimension is conformed by each location field?
For this one, I denormalized location information to each dimension.

Code:

    |----------|       |----------|
    | Dim_Cust |------ | Dim_Shop |
    |----------|       |----------|

[Dr. Warehouse]

Maybe you could explain a bit about what you are trying to achieve? What are you measuring? How do you want to model your fact table? That usually has a big impact on what the correct solution to your problem is.

Snowflaking should generally be avoided. Your second example still resembles part of a snowflake (dim to dim) though.

[ngalemmo]

What do you mean by 'location'? Is it some form of geo-coding (lat/long, region code…)?

The location needs to remain in context. One is the presumed location of the customer and the other the location of the store. Location itself doesn't necessarily require its own dimension. You only consider a dimension if the entity itself has attributes. If it is just a code and description those are usually maintained within the customer and store dimensions.

[Sponsored content]