Snow Flake Schema

Snow Flake Schema is the Structure which is widely used in Database Design.

The main drawback in Star Schema is it can contain one fact table and many dimension tables but which have to be connected with the fact table .So whenever we need a Subdivision of a Field in Dimension (a Lookup) it will create a problem as it has to be added to the same dimension table which will Reduce the performance of DB.

For eg : A field called State_cd (state code) needs a description field Say State_name , Now this can be given using one more dimension table which links with the Parent dimension using a Pk-Fk relationship(using State_cd)

Let us understand this by using the Below Example:
Here we can see that the item dimension table is further joined with another dimension table called Supplier which gives the Supplier type. So whenever the Supplier type column is needed , we have to join item dimension table with the Supplier Dimension table using the Field called Supplier_Key .