Star Schema

`
-- Star schema structure

-- Fact Table: Sales (contains measures and foreign keys)
CREATE TABLE fct_sales (
  sale_id BIGINT PRIMARY KEY,
  date_id INT NOT NULL,
  product_id INT NOT NULL,
  customer_id INT NOT NULL,
  store_id INT NOT NULL,
  revenue DECIMAL(10, 2),
  quantity INT,
  discount DECIMAL(5, 2),
  FOREIGN KEY (date_id) REFERENCES dim_date,
  FOREIGN KEY (product_id) REFERENCES dim_product,
  FOREIGN KEY (customer_id) REFERENCES dim_customer,
  FOREIGN KEY (store_id) REFERENCES dim_store
);

-- Dimension Tables (contain attributes for analysis)
CREATE TABLE dim_date (
  date_id INT PRIMARY KEY,
  calendar_date DATE,
  year INT,
  quarter INT,
  month INT,
  day_of_week VARCHAR(10),
  is_holiday BOOLEAN
);

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

CREATE TABLE dim_customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255),
  segment VARCHAR(50),
  region VARCHAR(50),
  lifetime_value DECIMAL(12, 2)
);

-- Simple analytical query using star schema
SELECT 
  d.year,
  d.quarter,
  p.category,
  c.region,
  SUM(f.revenue) as total_revenue,
  SUM(f.quantity) as units_sold,
  COUNT(*) as transaction_count
FROM fct_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_customer c ON f.customer_id = c.customer_id
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category, c.region;
`