Identify the business process, such as daily product sales, to determine the fact table's purpose. 2. Define the fact table, like fact_sales, containing measurable data (quantity_sold, total_amount) and foreign keys (date_id, product_id). 3. Design dimension tables—dim_product, dim_customer, dim_store, and dim_date—with descriptive attributes and primary keys linked to the fact table. 4. Apply key design principles: use denormalized dimensions, surrogate keys, handle NULLs with defaults, and maintain one fact table connected to multiple dimensions. 5. Write analytical queries by joining the fact table with dimensions to generate insights, such as total sales by month and category. The star schema relies on a central fact table linked to descriptive dimension tables for efficient reporting and fast query performance.

Designing a star schema in a data warehouse using SQL involves organizing your data into fact tables and dimension tables to support fast querying and analytical reporting. Here's how to do it step by step:

1. Identify the Business Process

Start by understanding the core business process you want to analyze—like sales, orders, website clicks, or inventory changes. This determines what your fact table will represent.

For example:

You're building a sales data warehouse. The key process is "daily product sales".

2. Define the Fact Table

The fact table stores measurable, quantitative data (like sales amount, quantity) and links to dimensions via foreign keys.

Example: fact_sales table

CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    date_id INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    store_id INT NOT NULL,
    quantity_sold INT,
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(10,2),
    discount_amount DECIMAL(10,2),

    -- Foreign key constraints
    CONSTRAINT fk_date FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id),
    CONSTRAINT fk_store FOREIGN KEY (store_id) REFERENCES dim_store(store_id)
);

? Facts (measures): quantity_sold, total_amount, discount_amount
? Foreign keys: Link to dimension tables

3. Design Dimension Tables

Dimensions provide context to facts—like who, what, where, when. Each dimension has a primary key that links to the fact table.

a. dim_product – Describes products

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(100),
    brand VARCHAR(100),
    unit_price DECIMAL(10,2)
);

b. dim_customer – Customer details

CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100)
);

c. dim_store – Store locations

CREATE TABLE dim_store (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(255),
    location_city VARCHAR(100),
    location_state VARCHAR(100),
    manager_name VARCHAR(255)
);

d. dim_date – Date dimension (very common)

Pre-populate with dates (e.g., one row per day for 10 years)

CREATE TABLE dim_date (
date_id INT PRIMARY KEY,  -- e.g., 20250405
full_date DATE,
day_of_week VARCHAR(10),
day_of_month INT,
month_name VARCHAR(20),
month_number INT,
quarter INT,
year INT,
is_weekend BOOLEAN
);

? This allows filtering by month, quarter, etc., without using SQL date functions.

4. Key Design Principles

• Denormalized dimensions: It’s okay (and encouraged) to duplicate data in dimensions (e.g., category in dim_product) for faster queries.
• Surrogate keys: Use integer primary keys (like product_id) instead of natural keys (like product name) for stability and performance.
• Avoid NULLs where possible: Fill with "Unknown" or "Not Applicable" in dimensions.
• One fact table, multiple dimensions: Star schema = one central fact surrounding dimensions.

5. Query Example

Now you can write fast analytical queries:

SELECT 
    d.year,
    d.month_name,
    p.category,
    SUM(fs.total_amount) AS total_sales
FROM fact_sales fs
JOIN dim_date d ON fs.date_id = d.date_id
JOIN dim_product p ON fs.product_id = p.product_id
WHERE d.year = 2024
GROUP BY d.year, d.month_name, p.category
ORDER BY total_sales DESC;

This query rolls up sales by month and product category—typical for dashboards.

Summary of Steps:

• ? Pick the business process → define the fact
• ? Choose grain (e.g., one row per sale)
• ? Create fact table with numeric measures foreign keys
• ? Build dimension tables with descriptive attributes
• ? Connect fact to dimensions via foreign keys
• ? Write queries joining fact dimensions

It's not complex in SQL syntax, but the design matters. Get the model right, and your reports will be fast and intuitive.

Basically: One fact, many dimensions, all linked with keys.

The above is the detailed content of How do you design a star schema for a data warehouse using SQL?. For more information, please follow other related articles on the PHP Chinese website!