🛒 E-Commerce Advanced Database Schema

Schema SQL completo e profissional para e-commerce com queries analíticas avançadas, stored procedures, triggers e otimizações de performance.

🎯 Overview

Este projeto demonstra conhecimento avançado de SQL com foco em:

Schema Design complexo e normalizado
Stored Procedures para lógica de negócio
Triggers para automação
Window Functions para análises
CTEs Recursivos para hierarquias
Índices estratégicos para performance
Queries analíticas (RFM, Cohort Analysis, etc)

📊 Comparação: Sistema Escolar vs E-Commerce

Aspecto	Sistema Escolar (Antigo)	E-Commerce (Novo)
Tabelas	1-2 tabelas	16+ tabelas
Relacionamentos	Básicos	Complexos (1:N, N:M, hierárquicos)
Stored Procedures	Nenhuma	5+ procedures
Triggers	Nenhum	10+ triggers
Views	Nenhuma	3+ views analíticas
Queries Complexas	SELECT simples	Window functions, CTEs, subconsultas
Índices	Básicos	Estratégicos + Full-text
Análises	Nenhuma	RFM, Cohort, ABC, Churn
Nível	Júnior	Pleno/Sênior

🏗️ Arquitetura do Schema

Core Tables (12 tabelas)

customers ─┬─→ addresses
           ├─→ orders ──→ order_items ──→ products ──→ categories
           ├─→ cart_items                                    │
           ├─→ wishlist                                       │
           ├─→ product_reviews                                │
           └─→ coupon_usage ──→ coupons                      │
                                                              │
products ──┼─→ product_images                                 │
           └─→ product_metrics                                │
                                                              │
orders ────→ payment_transactions                            │
           └─→ order_status_history                          │
                                                              │
Analytical Tables:                                            │
- customer_lifetime_value                                     │
- product_metrics                                             │

Relacionamentos Principais

1:N - Customer → Orders, Product → Reviews
N:M - Customer ↔ Products (via cart, wishlist, orders)
Hierárquico - Categories (parent-child)
Self-referencing - Categories → parent_category

📁 Estrutura de Arquivos

ecommerce-database/
├── scripts/
│   ├── 01_create_schema.sql         # DDL - 16+ tabelas
│   ├── 02_stored_procedures.sql     # 5 procedures complexas
│   ├── 03_triggers.sql              # 10 triggers automáticos
│   ├── 04_views_and_analytics.sql   # Views + 10 queries analíticas
│   ├── 05_sample_data.sql           # Dados de exemplo
│   └── 06_indexes_optimization.sql  # Índices estratégicos
│
├── docs/
│   ├── SCHEMA_DIAGRAM.md            # Diagrama ER
│   ├── QUERY_EXAMPLES.md            # Exemplos de uso
│   └── PERFORMANCE_GUIDE.md         # Guia de otimização
│
└── README.md

🚀 Como Usar

1. Criar o Database

# Conectar ao MySQL
mysql -u root -p

# Executar scripts na ordem
source scripts/01_create_schema.sql
source scripts/02_stored_procedures.sql
source scripts/03_triggers.sql
source scripts/04_views_and_analytics.sql
source scripts/05_sample_data.sql

2. Verificar Instalação

USE ecommerce_advanced;

-- Ver todas as tabelas
SHOW TABLES;

-- Ver procedures
SHOW PROCEDURE STATUS WHERE Db = 'ecommerce_advanced';

-- Ver triggers
SHOW TRIGGERS;

-- Testar dados
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;

💡 Features Avançadas

1. Stored Procedures - Automação de Negócio

Processar Pedido Completo

CALL sp_process_order(
    1,                    -- customer_id
    1,                    -- shipping_address_id
    1,                    -- billing_address_id
    'CREDIT_CARD',        -- payment_method
    'WELCOME10',          -- coupon_code
    @order_id,            -- OUT: order_id gerado
    @total_amount         -- OUT: total calculado
);

SELECT @order_id, @total_amount;

Calcular RFM Scores

-- Segmenta clientes por comportamento de compra
CALL sp_calculate_rfm_scores();

SELECT * FROM customer_lifetime_value
WHERE rfm_segment = 'Champions';

Produtos com Baixo Estoque

CALL sp_check_low_stock(20);  -- threshold = 20 unidades

Recomendações Personalizadas

CALL sp_get_product_recommendations(1, 5);  -- customer_id, limit

2. Triggers - Regras Automáticas

✅ Validação automática de estoque ao adicionar no carrinho
✅ Atualização de métricas após reviews
✅ Auditoria de mudanças de status de pedidos
✅ Prevenção de exclusão de produtos com pedidos
✅ Cálculo automático de totais

-- Exemplo: Tentar adicionar produto sem estoque
INSERT INTO cart_items (customer_id, product_id, quantity)
VALUES (1, 5, 999999);
-- ERROR: Insufficient stock for this product

3. Queries Analíticas Avançadas

RFM Analysis (Segmentação de Clientes)

SELECT 
    customer_id,
    email,
    rfm_segment,
    recency_score,
    frequency_score,
    monetary_score,
    total_revenue as lifetime_value
FROM customer_lifetime_value clv
JOIN customers USING (customer_id)
WHERE rfm_segment IN ('Champions', 'Loyal')
ORDER BY total_revenue DESC;

Resultado:

Champions    - Alto valor, compra recente e frequente
Loyal        - Clientes fiéis
At Risk      - Clientes valiosos que não compram há tempo
Lost         - Clientes que não retornam

Cohort Analysis (Retenção)

-- Analisa retenção mês a mês
WITH customer_cohorts AS (
    SELECT 
        customer_id,
        DATE_FORMAT(created_at, '%Y-%m') as cohort_month
    FROM customers
)
SELECT 
    cohort_month,
    months_since_signup,
    COUNT(DISTINCT customer_id) as active_customers,
    ROUND(COUNT(*) * 100.0 / 
        LAG(COUNT(*)) OVER (PARTITION BY cohort_month ORDER BY months_since_signup), 2
    ) as retention_rate
FROM order_months
GROUP BY cohort_month, months_since_signup;

Market Basket Analysis

-- Produtos frequentemente comprados juntos
SELECT 
    p1.product_name as product_a,
    p2.product_name as product_b,
    COUNT(*) as times_bought_together
FROM order_items oi1
JOIN order_items oi2 
    ON oi1.order_id = oi2.order_id 
    AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.product_id
JOIN products p2 ON oi2.product_id = p2.product_id
GROUP BY product_a, product_b
HAVING times_bought_together >= 5
ORDER BY times_bought_together DESC;

Window Functions - Running Totals

-- Receita diária com totais acumulados e média móvel
SELECT 
    sale_date,
    total_revenue,
    SUM(total_revenue) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_revenue,
    AVG(total_revenue) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM vw_daily_sales_summary
ORDER BY sale_date DESC;

ABC Analysis

-- Classifica produtos por contribuição de receita (Pareto 80/20)
WITH product_revenue AS (
    SELECT 
        product_id,
        product_name,
        SUM(total_price) as total_revenue
    FROM products p
    JOIN order_items oi USING (product_id)
    GROUP BY product_id, product_name
)
SELECT 
    product_name,
    total_revenue,
    CASE 
        WHEN cumulative_percentage <= 80 THEN 'A'  -- Top 80% revenue
        WHEN cumulative_percentage <= 95 THEN 'B'  -- Next 15%
        ELSE 'C'                                   -- Bottom 5%
    END as abc_category
FROM product_revenue;

Churn Prediction

-- Identifica clientes em risco de abandono
SELECT 
    customer_id,
    email,
    days_since_last_order,
    avg_days_between_orders,
    CASE
        WHEN days_since_last_order > 2 * avg_days_between_orders 
            THEN 'High Risk'
        WHEN days_since_last_order > 1.5 * avg_days_between_orders 
            THEN 'Medium Risk'
        ELSE 'Low Risk'
    END as churn_risk
FROM customer_stats
ORDER BY days_since_last_order DESC;

🎨 Views Materializadas

Customer 360 View

SELECT * FROM vw_customer_360
WHERE lifetime_value > 10000
ORDER BY lifetime_value DESC;

Campos incluídos:

Total de pedidos
Lifetime value
Dias desde último pedido
Avaliações escritas
Items na wishlist

Product Performance Dashboard

SELECT * FROM vw_product_performance
WHERE stock_status = 'Low Stock'
ORDER BY revenue DESC;

🔍 Otimizações de Performance

Índices Estratégicos

-- Composite indexes para queries comuns
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_order_items_product ON order_items(product_id, order_id);

-- Full-text search
ALTER TABLE products 
ADD FULLTEXT INDEX ft_product_search (product_name, description);

-- Busca textual rápida
SELECT * FROM products
WHERE MATCH(product_name, description) 
AGAINST ('smartphone' IN NATURAL LANGUAGE MODE);

Query Optimization Tips

Use EXPLAIN para analisar queries
Evite SELECT * em produção
Use LIMIT em queries grandes
Crie índices em colunas de JOIN e WHERE
Use UNION ALL em vez de UNION quando possível

📈 Métricas e KPIs

Dashboard Queries

Revenue Today

SELECT SUM(total_amount) as revenue_today
FROM orders
WHERE DATE(order_date) = CURRENT_DATE
  AND order_status != 'CANCELLED';

Top Selling Products

SELECT 
    p.product_name,
    SUM(oi.quantity) as units_sold,
    SUM(oi.total_price) as revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id
ORDER BY revenue DESC
LIMIT 10;

Customer Acquisition

SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    COUNT(*) as new_customers
FROM customers
WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
GROUP BY month
ORDER BY month;

🎓 Conceitos SQL Demonstrados

DDL (Data Definition Language)

✅ CREATE TABLE com constraints
✅ FOREIGN KEY com ON DELETE CASCADE
✅ CHECK constraints
✅ UNIQUE constraints
✅ ENUMs para tipos fixos

DML (Data Manipulation Language)

✅ INSERT com múltiplos valores
✅ UPDATE com JOINs
✅ DELETE com subconsultas
✅ UPSERT (ON DUPLICATE KEY UPDATE)

Queries Avançadas

✅ Window Functions (ROW_NUMBER, RANK, NTILE)
✅ CTEs (Common Table Expressions)
✅ CTEs Recursivos (hierarquias)
✅ Subconsultas correlacionadas
✅ JOINs complexos

Programação SQL

✅ Stored Procedures com parâmetros IN/OUT
✅ Triggers (BEFORE/AFTER INSERT/UPDATE/DELETE)
✅ Variables e controle de fluxo
✅ Transações (START TRANSACTION, COMMIT, ROLLBACK)
✅ Tratamento de erros (SIGNAL SQLSTATE)

🚀 Para Recrutadores

Este projeto demonstra:

Design de Schema - Normalização, relacionamentos complexos
SQL Avançado - Window functions, CTEs, subconsultas
Business Logic - Stored procedures com lógica real
Automação - Triggers para integridade de dados
Analytics - RFM, Cohort, ABC, Churn prediction
Performance - Índices estratégicos, otimizações
Best Practices - Constraints, transactions, error handling

Nível: Pleno/Sênior
Complexidade: High
Impacto no portfólio: ⭐⭐⭐⭐⭐

📝 Próximos Passos

Adicionar particionamento de tabelas
Implementar sharding strategy
Criar data warehouse (OLAP)
Adicionar backup/restore scripts
Performance benchmarks
Migração para PostgreSQL

📖 Recursos de Aprendizado

MySQL Documentation: https://dev.mysql.com/doc/
Window Functions: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
CTEs: https://dev.mysql.com/doc/refman/8.0/en/with.html
Stored Procedures: https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html

📄 Licença

MIT License

👤 Autor

Desenvolvido como projeto de portfólio demonstrando expertise em:

Database Design
Advanced SQL
Data Analytics
Performance Optimization
Business Intelligence

Name		Name	Last commit message	Last commit date
Latest commit History 2 Commits
docs		docs
scripts		scripts
LICENSE		LICENSE
README.md		README.md

License

yasmim-passos/sql-e-commerce-database-schema

Folders and files

Latest commit

History

Repository files navigation