0

To start, I am trying to differentiate from Star Schema and Snowflake Schema by illustrating them. But am having trouble trying to normalizing the table to create the snowflake schema. The attached image is the Star Schema enter image description here

I tried creating another dim table for dimcustomer, but am not sure what I could name the table. Please, any help would be appreciated.

Snowflake Schema enter image description here

AdrianAndrews
  • 33
  • 1
  • 6
  • Can you be more specific about what you are asking? Just about another name for DimCustomer? Why would you want another name? Or is it more that you are looking to split Customer out into normalised tables to make them a snowflake? If the latter, you could make separate DimCity and DimCountryRegion tables which link to each other and then to DimCustomer. That'd be more of a snowflake, but I wouldnt' recommend it. – Rich Apr 23 '17 at 20:37
  • I have added a diagram with the dimCity and connected it to the dimCustomer. But I am not sure which other tables I can normalize – AdrianAndrews Apr 24 '17 at 01:45
  • Well, date is currently by date, so you could certainly make a month table and a year table. But why? – Rich Apr 24 '17 at 07:24
  • Can you tell us why you are trying to normalising your schema? A bit of background info on purpose will help us make recommendations. Has table size become an issue? If so why (slow query performance? slow etl? etc)? – David Rushton Apr 24 '17 at 11:09

1 Answers1

1

Your star schema is good, don't normilize it into a snowflake schema.

This is a typical mistake made by people with strong background in relational databases. They often perceive denormalized dimensions as "inefficient" and try to "fix" them by normalizing. What they miss is that dimensional models and OLTP databases have different efficiency criteria (query speed vs storage efficiency).

Snowflake schema is usually non-necessary and counter-productive. It will complicate your design and negatively impact your model performance. Practically speaking, I only use snowflake structure when I have to share a dimension between fact tables with different granularities.

RADO
  • 7,733
  • 3
  • 19
  • 33