Schemas and Data Modeling


Schema is a named container for database objects, which allows you to group objects into logical collections.

  • used to describe the structure of data tables and the layout of relational designs
  • created to hold the data that is sourced in our primary systems,
    • such as our online ordering system database that feeds data to the warehouse
  • In data warehousing,
    • data is transformed to meet different specifications
    • so while it is still relational, it might not be normalized
    • use schemas to describe these relational designs
      • known as data models
  • Normalization is a design process for building databases
  • Two common schemas to relate data tables to each other in data warehousing:
    1. snowflake schema
    2. star schema
    • key similarity between these schemas is the use of fact tables and dimension tables
      • uniquely designed to hold facts and information (dimensions) that are sourced from other data systems
      • Both use joins to relate these two types of tables
    • both schemas use the same type, or rules, of normalization
      • Key fields relate primary and foreign to bridge a gap between two tables

Fact Tables

Fact table is a table that holds the “facts” about a particular business process or event and contains keys to relate to the other tables.

Example Fact Table Showing Keys and Facts

  • fact table named Fact_ProductSales
  • contains the facts related to a sale:
    • product sold
    • date and time the product was ordered
    • customer who ordered the product
    • salesperson who sold the product
    • how much of the product was ordered
  • Every time a person makes an order, we have a new fact record
    • can be measured, representing an event or a transaction of some kind
    • certainly includes quantifiable data
  • fact table, being all keys, links to the dimensional data in the dimension table, which is categorical or qualitative

Dimension Table

Dimension table is a table that holds attributes or the categorial information that supports the fact tables.

  • information contained within a dimension table gives us more information related to the fact table
    • providing us with a full set of data

Example Dimension Table

  • related to the Fact_ProductSales table
  • tables are used to meaningfully categorize data by customer, salesperson, and product
  • When a data professional links the facts to the dimensional data,
    • we are providing more meaningful data sets for people to use in the organization for their reporting and analysis

Star Schema

Star schema is the relational model of fact and dimension tables that looks like a star, in which all dimension tables are joined to a single fact table.

  • one of the simplest schemas to use or read
  • generally use denormalized dimension tables for performance and simplicity

Snowflake Schema

Snowflake schema is the relational model of fact and dimension tables that looks like a snowflake, in which dimension tables are joined to a single fact table and also other dimension tables.

  • unlike the star schema, dimension tables in a snowflake schema might also be tied to other dimension tables, creating the appearance of a snowflake
  • use normalized dimension tables to reduce redundancy
  • looks more similar to a normalized relational database

Example Snowflake Schema