What is the difference between star schema and snow flake schema ?and when we use those schema’s?
Star Schema : Star Schema is a relational database schema for representing multimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.
Snowflake Schema : A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables.
In a star schema every dimension will have a primary key.
# In a star schema, a dimension table will not have any parent table.
# Whereas in a snow flake schema, a dimension table will have one or more parent tables.
# Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
# Whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.