E-commerce Database Design

A comprehensive database architecture for scalable e-commerce platforms, featuring both operational (OLTP) and analytical (OLAP) systems.

📋 Overview

This project implements a complete data model for an e-commerce platform, balancing data integrity for daily operations with high-performance analytics capabilities.

🏗️ Architecture

Operational Model (OLTP)

Purpose: Daily transaction processing and business operations

• Normalized schema (3rd Normal Form)
• Data integrity focused design
• Real-time operations support

Analytical Model (OLAP)

Purpose: Business intelligence and reporting

• Star schema design
• Optimized for aggregations and complex queries
• Historical tracking with SCD Type 2

📊 Database Schema

Key Entities & Relationships

Operational Tables:

• regions - Geographical hierarchy (country, state, city)
• categories - Recursive hierarchical product categorization
• products - Product catalog with pricing and availability
• customers - Customer master data with regional association
• orders - Order header information with status tracking
• order_items - Order line items with historical pricing
• transactions - Payment processing and transaction history

Analytical Tables (Star Schema):

• fact_sales - Central fact table for sales metrics
• dim_date - Enriched time dimension with fiscal calendar
• dim_product - Product dimension with SCD Type 2 support
• dim_customer - Customer dimension with segmentation
• dim_region - Geographical dimension
• dim_category - Category dimension with hierarchy support

🔗 Key Relationships

1. Categories → Recursive hierarchy via parent_category_id
2. Products → Belong to one category (many-to-one)
3. Customers → Associated with one region (many-to-one)
4. Orders → Created by one customer (many-to-one)
5. Order Items → Link orders and products (many-to-one)
6. Transactions → Associated with orders (one-to-one/many)

⚡ Performance Optimizations

Operational Indexes:

-- Fast product categorization
CREATE INDEX idx_products_category ON products(category_id);

-- Active product filtering
CREATE INDEX idx_products_active ON products(is_available) 
WHERE is_available = TRUE;

-- Customer order history
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Analytical Indexes:

-- Time-based analysis
CREATE INDEX idx_fact_sales_date ON fact_sales(date_id);

-- Product performance
CREATE INDEX idx_fact_sales_product ON fact_sales(product_id);

-- Regional and category analysis
CREATE INDEX idx_fact_sales_category_region ON fact_sales(category_id, region_id);

📈 Advanced Features

Historical Data Management

• Snapshot Approach: order_items.unit_price captures price at time of sale
• SCD Type 2: Dimensions track historical changes with effective/expiration dates
• Price History: Separate product_price_history table for audit trail

Design Trade-offs

• Operational: Full normalization for data integrity
• Analytical: Strategic denormalization for query performance
• Example: fact_sales includes category_id to eliminate JOINs

🚀 Scalability Considerations

• Partitioning: fact_sales by date for large datasets
• Materialized Views: Pre-aggregated metrics for common reports
• Covering Indexes: Optimized for critical query patterns
• Monitoring: Continuous performance tuning based on usage patterns

🛠️ Installation & Usage

1. Database Setup:

-- Execute the schema creation script
\i scheme.sql

2. Sample Queries:

-- Monthly sales by category
SELECT 
    d.year, d.month, c.name AS category,
    SUM(fs.quantity) AS total_quantity,
    SUM(fs.revenue) AS total_revenue
FROM fact_sales fs
JOIN dim_date d ON fs.date_id = d.date_id
JOIN dim_category c ON fs.category_id = c.category_id
GROUP BY d.year, d.month, c.name
ORDER BY d.year, d.month, total_revenue DESC;

📝 Best Practices Implemented

• ✅ Proper indexing strategy
• ✅ Foreign key constraints for data integrity
• ✅ SCD Type 2 for historical tracking
• ✅ Balanced normalization/denormalization
• ✅ Comprehensive data types and constraints

📄 License

This project is open source and available under the MIT License.

---

Author: Peter Leukanič
Contact: Mail in profile info