Star and snowflake schema

Database Design

Lis Sulmont

Curriculum Manager

Star schema

Dimensional modeling: star schema

Fact tables

  • Holds records of a metric
  • Changes regularly
  • Connects to dimensions via foreign keys

Dimension tables

  • Holds descriptions of attributes
  • Does not change as often

Example:

  • Supply books to stores in USA and Canada
  • Keep track of book sales
Database Design

Star schema example

Star schema diagram

Database Design

Snowflake schema (an extension)

Snowflake schema diagram

Database Design

Same fact table, different dimensions

$$

Star schema diagram

Star schemas: one dimension

$$

Snowflake schema diagram

Snowflake schemas: more than one dimension

Because dimension tables are normalized

Database Design

What is normalization?

  • Database design technique
  • Divides tables into smaller tables and connects them via relationships
  • Goal: reduce redundancy and increase data integrity
Database Design

What is normalization?

  • Database design technique
  • Divides tables into smaller tables and connects them via relationships
  • Goal: reduce redundancy and increase data integrity

Identify repeating groups of data and create new tables for them

Database Design

Book dimension of the star schema

$$ Book dimension of the star schema

Most likely to have repeating values:

  • Author
  • Publisher
  • Genre
Database Design

Book dimension of the snowflake schema

Book dimension of the snowflake schema

Database Design

Store dimension of the star schema

$$ Store dimension of the star schema

  • City
  • State
  • Country
Database Design

Store dimension of the snowflake schema

Store dimension of the snowflake schema

Book dimension of the snowflake schema

Database Design

$$ $$ $$ $$ $$ Time dimension of the star schema

$$ Time dimension of the snowflake schema

Database Design

$$

Snowflake schema

Database Design

Let's practice!

Database Design

Preparing Video For Download...