Entity Relational Modeling
vs.
Dimensional Modeling

Star Schema

• 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.

Star Join

Snowflake schema

• A 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 star schema example we had 4 dimensions like location, product, time, organization and a fact table(sales).
  
  
• In snow flake schema, the example has 4 dimension tables, 4 lookup tables and 1 fact table. The reason is that hierarchies(category, branch, state, and month) are being broken out of the dimension tables(PRODUCT, ORGANIZATION, LOCATION, and TIME) respectively and shown separately.
  
  
• In OLAP, this snow flake schema approach increases the number of joins and poor performance in retrieval of data. In few organizations, they try to normalize the dimension tables to save space. Since dimension tables hold less space, snow flake schema approach may be avoided.

Important aspects of Star Schema & Snow Flake Schema

• 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.

Back to Entity Relational Modeling   vs.  Dimensional Modeling Index