Star schema concept in SQL

Introduction

A star schema helps organize data simply so you can find answers fast. It makes reports and analysis easier to understand.

When you want to build a simple and fast database for reporting.

When you have a main subject like sales and details like customers or products.

When you want to make it easy for non-technical users to explore data.

When you need to improve query speed by reducing complex joins.

When you want to create clear and organized dashboards.

Syntax

SQL

-- Star schema has one big fact table in the center
-- and several smaller dimension tables around it.

-- Example structure:
CREATE TABLE FactSales (
  SaleID INT PRIMARY KEY,
  ProductID INT,
  CustomerID INT,
  DateID INT,
  SalesAmount DECIMAL(10,2)
);

CREATE TABLE DimProduct (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Category VARCHAR(50)
);

CREATE TABLE DimCustomer (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100),
  Region VARCHAR(50)
);

CREATE TABLE DimDate (
  DateID INT PRIMARY KEY,
  Date DATE,
  Month VARCHAR(20),
  Year INT
);

The fact table holds numbers like sales or counts.

The dimension tables hold details like names or dates.

Examples

This fact table stores order details with keys linking to dimensions.

SQL

CREATE TABLE FactOrders (
  OrderID INT PRIMARY KEY,
  ProductID INT,
  CustomerID INT,
  OrderDateID INT,
  Quantity INT
);

This dimension table stores product details for easy lookup.

SQL

CREATE TABLE DimProduct (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Brand VARCHAR(50)
);

This dimension table stores date information to analyze trends over time.

SQL

CREATE TABLE DimDate (
  DateID INT PRIMARY KEY,
  Date DATE,
  Quarter VARCHAR(10),
  Year INT
);

Sample Program

This example shows how the star schema tables connect and how to query them to get sales details with names and dates.

SQL

-- Simple star schema example
CREATE TABLE FactSales (
  SaleID INT PRIMARY KEY,
  ProductID INT,
  CustomerID INT,
  DateID INT,
  SalesAmount DECIMAL(10,2)
);

CREATE TABLE DimProduct (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100)
);

CREATE TABLE DimCustomer (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100)
);

CREATE TABLE DimDate (
  DateID INT PRIMARY KEY,
  Date DATE
);

-- Sample query to get sales with product and customer names
SELECT
  d.Date,
  p.ProductName,
  c.CustomerName,
  f.SalesAmount
FROM FactSales f
JOIN DimProduct p ON f.ProductID = p.ProductID
JOIN DimCustomer c ON f.CustomerID = c.CustomerID
JOIN DimDate d ON f.DateID = d.DateID
WHERE d.Date BETWEEN '2024-01-01' AND '2024-01-31';

Important Notes

Keep dimension tables small and descriptive for easy understanding.

Fact tables usually have many rows and numeric data for calculations.

Star schema is great for fast queries but may duplicate some data in dimensions.

Summary

A star schema organizes data with one fact table and many dimension tables.

It makes data easy to explore and speeds up reporting.

Use it when you want simple, clear, and fast data models for business analysis.