Star v/s Snowflake schemas

A schema is a collection of database objects, including tables, views, indexes, and synonyms.
Will try to describe some of the models in this post, namely: Star schema and Snowflake Schema

Star Schema:
The star schema is perhaps the simplest data warehouse schema.

It is called a star schema because the entity-relationship diagram of this schema resembles a star, with points radiating from a central table.
The center of the star consists of a large fact table and the points of the star are the dimension tables.



 

Snowflake schema:
In data warehousing, snowflaking is a form of dimensional modeling in which dimensions are stored in multiple related dimension tables. 
"Snowflaking" is a method of normalising the dimension tables in a star schema.





Snowflake v/s Star:

• Snowflake model uses normalized data, i.e. the data is organized inside the database in order to eliminate redundancy and thus helps to reduce the amount of data.
• The Snowflake model has higher number of joins between dimension table and then again the fact table and hence the performance is slower.
• Star schema is good for data marts with simple relationships.
• When dimension table is relatively big in size, snowflaking is better as it reduces space.
• In Snowflake: Dimension Tables are in Normalized form but Fact Table is still in De-Normalized form.
• In Star: Both Dimension and Fact Tables are in De-Normalized form
• In Snowflake: Approach is bottom up.
• In Star: Approach is top down.