Objetivo
Ejercicio ultra-completo para normalizar un sistema de e-commerce / retail con funcionalidades
reales:
productos con variantes, pedidos con múltiples líneas, promociones, impuestos, stock por depósito, devoluciones,
marketplace multi-vendedor,
pagos, envíos y auditoría. Ideal para mostrar 1FN → 3FN en un entorno real.
Casos reales que cubrimos
- Pedido con varias líneas y variantes (talle/color/SKU).
- Precios en distintas listas (público, mayorista), descuentos por cupón o por línea.
- Impuestos por producto (distintas alícuotas) y retenciones locales.
- Stock en múltiples almacenes/depósitos y movimientos de stock (recepción, reserva, despacho, devolución).
- Pagos parciales, reembolsos y conciliación bancaria.
- Marketplace: vendedores múltiples con comisiones.
- Trazabilidad: cada cambio de estado queda auditado.
Tabla original (sin normalizar) — ejemplo típico de sistema creciente
La tabla mezcla datos maestros, transaccionales y listas en las mismas filas.
| PedidoID |
Fecha |
Usuario |
Email |
Direcciones |
Items |
Variantes |
Cant |
PrecioUnit |
Impuestos |
Cupon |
Estado |
Pago |
Envio |
StockMov |
| P1001 |
2024-11-10 |
María López |
maria@mail.com |
Calle Falsa 123 (envío), Av. Central 45 (fact) |
SKU-RED-XL, SKU-BLUE-M |
Color=Rojo|Talle=XL, Color=Azul|Talle=M |
1,2 |
12000,8000 |
IVA21%, IVA10% |
BLACKFRI20 |
Pendiente |
Tarjeta:Autorizada |
Transportadora X (ID:TRK123) |
Reserva stock: WH1:SKU-RED-XL:1; WH2:SKU-BLUE-M:2 |
| P1002 |
2024-11-11 |
Invitado |
cliente2@mail.com |
Calle 50 #10 |
SKU-GIFT |
Color=None |
1 |
5000 |
IVA21% |
|
Pagado |
Transferencia:Recibida |
Retiro en sucursal |
Salida stock: WH1:SKU-GIFT:1 |
| P1003 |
2024-11-12 |
Comercial SA |
ventas@comercial.com |
Distribuidor 1 |
SKU-BULK |
Pack=10 |
5 |
7000 |
Exento |
NET30 |
Enviado |
Factura A emitida |
Transportadora Y (TRK888) |
Salida stock: WH2:SKU-BULK:5 |
Problemas detectados
- Listas en
Items, Cant, PrecioUnit → violación de 1FN.
- Información de direcciones mezclada (envío vs facturación).
- Stock y movimientos en texto libre, sin vincular a depósitos ni tipos de movimiento.
- Pagos y estados no estructurados (tarjeta, transferencia, reembolsos).
- Marketplace y vendedor no diferenciados por línea de pedido.
1FN — Datos atómicos
Expandimos cada pedido por línea de item (Pedido × Línea).
| PedidoID |
LíneaID |
Fecha |
Usuario |
Email |
ItemSKU |
Variante |
Cantidad |
PrecioUnit |
Impuesto |
Cupon |
Estado |
| P1001 |
P1001-1 |
2024-11-10 |
María López |
maria@mail.com |
SKU-RED-XL |
Color=Rojo;Talle=XL |
1 |
12000 |
IVA21% |
BLACKFRI20 |
Pendiente |
| P1001 |
P1001-2 |
2024-11-10 |
María López |
maria@mail.com |
SKU-BLUE-M |
Color=Azul;Talle=M |
2 |
8000 |
IVA10% |
BLACKFRI20 |
Pendiente |
| P1002 |
P1002-1 |
2024-11-11 |
Invitado |
cliente2@mail.com |
SKU-GIFT |
Color=None |
1 |
5000 |
IVA21% |
|
Pagado |
| P1003 |
P1003-1 |
2024-11-12 |
Comercial SA |
ventas@comercial.com |
SKU-BULK |
Pack=10 |
5 |
7000 |
Exento |
NET30 |
Enviado |
Observaciones
- Al separar por línea podemos aplicar impuestos/descuentos por línea.
- Persisten redundancias: cliente repetido, impuesto en texto.
- Falta separar direcciones, pagos, stock y vendedores en tablas propias.
2FN — Eliminar dependencias parciales
Creamos entidades para clientes, productos (maestro), variantes, almacenes y pedidos.
Tablas principales (2FN)
Pedidos (Orders)
| OrderID |
Fecha |
UserID |
Estado |
Total |
| P1001 |
2024-11-10 |
U100 |
Pendiente |
...calculable |
| P1002 |
2024-11-11 |
GUEST-1 |
Pagado |
... |
| P1003 |
2024-11-12 |
U200 |
Enviado |
... |
Usuarios / Clientes (Users)
| UserID |
Nombre |
Email |
Tipo |
| U100 |
María López |
maria@mail.com |
Cliente |
| GUEST-1 |
Invitado |
cliente2@mail.com |
Invitado |
| U200 |
Comercial SA |
ventas@comercial.com |
Empresa |
Productos & SKUs
| ProductID |
Nombre |
Tipo |
| P-RED |
Remera Roja |
Prenda |
| P-BLUE |
Remera Azul |
Prenda |
| P-GIFT |
Gift Card Físico |
Regalo |
Variants / SKUs
| SKUID |
ProductID |
Attrs |
Precio |
| SKU-RED-XL |
P-RED |
Color:Rojo;Talle:XL |
12000 |
| SKU-BLUE-M |
P-BLUE |
Color:Azul;Talle:M |
8000 |
| SKU-GIFT |
P-GIFT |
Valor:5000 |
5000 |
OrderLines (detalle de pedido)
| LineID |
OrderID |
SKUID |
Cantidad |
PrecioUnit |
DescuentoLinea |
| P1001-1 |
P1001 |
SKU-RED-XL |
1 |
12000 |
0 |
| P1001-2 |
P1001 |
SKU-BLUE-M |
2 |
8000 |
10% |
| P1002-1 |
P1002 |
SKU-GIFT |
1 |
5000 |
0 |
Almacenes (Warehouses) y Stock
| WarehouseID |
Nombre |
Dirección |
| WH1 |
Principal |
Calle A 100 |
| WH2 |
Distribución |
Parque Industrial |
Observaciones 2FN
- Separamos entidades maestras y transaccionales: facilita cálculos agregados y mantiene integridad.
- Falta normalizar impuestos, pagos, envíos, marketplace (vendedores y comisiones) y movimientos de stock
como tablas independientes → 3FN.
3FN — Eliminar dependencias transitivas y entidades compuestas
Aquí se crean tablas independientes para impuestos, cupones, pagos, envíos, stockmovs, vendedores
y auditoría.
Impuestos (TaxRates)
| TaxID |
Nombre |
Porcentaje |
AplicableA |
| T-IVA21 |
IVA 21% |
21 |
Productos / Servicios |
| T-IVA10 |
IVA 10% |
10 |
Algunos textiles |
| T-EXENTO |
Exento |
0 |
Servicios exentos |
Cupones / Promociones
| CouponID |
Código |
Tipo |
Valor |
Vigencia |
| C-001 |
BLACKFRI20 |
Porcentaje |
20 |
2024-11-01 → 2024-11-30 |
| C-002 |
ENVIOFREE |
Envío |
0 |
... |
Pagos (Payments)
| PaymentID |
OrderID |
Fecha |
Método |
Monto |
Estado |
Referencia |
| PMT-9001 |
P1001 |
2024-11-10 |
Tarjeta |
...autorizado |
Autorizado |
TX123 |
| PMT-9002 |
P1002 |
2024-11-11 |
Transferencia |
5000 |
Recibido |
TRF-455 |
Envíos (Shipments)
| ShipmentID |
OrderID |
Transportista |
Tracking |
Costo |
DirecciónEnvioID |
| SHP-5001 |
P1001 |
TransX |
TRK123 |
1500 |
ADDR-1 |
StockMovements
| StockMovID |
SKUID |
WarehouseID |
TipoMov |
Cantidad |
Fecha |
Ref |
| SM-1 |
SKU-RED-XL |
WH1 |
RESERVA |
1 |
2024-11-10 |
P1001-1 |
| SM-2 |
SKU-BLUE-M |
WH2 |
RESERVA |
2 |
2024-11-10 |
P1001-2 |
| SM-3 |
SKU-GIFT |
WH1 |
SALIDA |
1 |
2024-11-11 |
P1002-1 |
Marketplace: Sellers y Comisiones
| SellerID |
Nombre |
Comisión% |
| S-001 |
Vendedor A |
10 |
| S-002 |
Marca Propia |
0 |
OrderLineSeller (vincula cada línea con el vendedor que la provee)
| LineID |
OrderLineID |
SellerID |
Comisión |
| LS-1 |
P1001-1 |
S-001 |
10% |
Auditoría (Logs de eventos por orden)
| LogID |
OrderID |
Usuario |
Acción |
Fecha |
| L-5001 |
P1001 |
system |
CREACIÓN |
2024-11-10T10:01 |
| L-5002 |
P1001 |
pm-gateway |
PAGO_AUTORIZADO |
2024-11-10T10:05 |
Ventajas 3FN
- Impuestos, pagos, envíos y stock son tablas independientes→ integridad y fácil conciliación.
- Marketplace y sellers por línea permiten pagar comisiones y reconciliar ventas por vendedor.
- Auditoría explícita facilita trazabilidad y cumplimiento (GDPR, fiscal).
Modelo final (resumen)
Tablas principales (resumen)
- Users: UserID (PK), Nombre, Email, Tipo (cliente/invitado/admin), FechaCreación
- Addresses: AddressID, UserID, Tipo (envío/fact), Calle, Ciudad, CP, País
- Products: ProductID, Nombre, Categoría, Marca
- SKUs: SKUID, ProductID, Atributos(JSON), PrecioBase, Peso
- Warehouses: WarehouseID, Nombre, Dirección
- StockMovements: StockMovID, SKUID, WarehouseID, TipoMov
(ENTRADA/RESERVA/SALIDA/DEVOLUCIÓN), Cantidad, Fecha, Ref
- Orders: OrderID, UserID, Fecha, Estado, TotalNeto, TotalImpuestos, TotalFinal
- OrderLines: LineID, OrderID, SKUID, Cantidad, PrecioUnit, DescuentoLinea
- Taxes: TaxID, Nombre, Porcentaje
- LineTaxes: ID, LineID, TaxID, Monto
- Coupons: CouponID, Código, Tipo, Valor, Vigencia
- Payments: PaymentID, OrderID, Fecha, Método, Monto, Estado
- Shipments: ShipmentID, OrderID, Transportista, Tracking, Costo, Estado
- Sellers: SellerID, Nombre, Comisión%
- OrderLineSeller: ID, LineID, SellerID, ComisiónMonto
- AuditLogs: LogID, OrderID, Actor, Acción, Metadata(JSON), Fecha
Relaciones clave
- Users 1:N Addresses
- Orders 1:N OrderLines
- OrderLines 1:N LineTaxes
- OrderLines N:1 SKUs → SKUs N:1 Products
- StockMovements por SKUID y Warehouse
- Payments 1:N per Order (pagos parciales posible)
- Shipments 1:N per Order (multi-paquete)
SQL — ejemplos y snippets útiles
a) Total ventas por SKU en un período
SELECT s.SKUID, p.Nombre, SUM(ol.Cantidad) AS CantVendida, SUM(ol.Cantidad * ol.PrecioUnit) AS Revenue
FROM OrderLines ol
JOIN SKUs s ON ol.SKUID = s.SKUID
JOIN Products p ON s.ProductID = p.ProductID
JOIN Orders o ON ol.OrderID = o.OrderID
WHERE o.Fecha BETWEEN '2024-11-01' AND '2024-11-30'
GROUP BY s.SKUID, p.Nombre
ORDER BY Revenue DESC;
b) Stock disponible por warehouse
-- Calcular saldo de stock por SKUID y Warehouse
SELECT sm.WarehouseID, sm.SKUID,
SUM(CASE WHEN sm.TipoMov IN ('ENTRADA') THEN sm.Cantidad
WHEN sm.TipoMov IN ('SALIDA','RESERVA') THEN -sm.Cantidad ELSE 0 END) AS StockActual
FROM StockMovements sm
GROUP BY sm.WarehouseID, sm.SKUID;
c) Reconcilación de pagos — pedidos con pagos pendientes
SELECT o.OrderID, o.TotalFinal, COALESCE(SUM(p.Monto),0) AS Pagado, o.TotalFinal - COALESCE(SUM(p.Monto),0) AS SaldoPendiente
FROM Orders o
LEFT JOIN Payments p ON p.OrderID = o.OrderID AND p.Estado IN ('Recibido','Compensado')
GROUP BY o.OrderID, o.TotalFinal
HAVING COALESCE(SUM(p.Monto),0) < o.TotalFinal;
d) Ventas por vendedor (marketplace) con comisiones
SELECT s.SellerID, s.Nombre,
SUM(ol.Cantidad * ol.PrecioUnit) AS GrossSales,
SUM(ols.ComisiónMonto) AS TotalComisiones,
SUM(ol.Cantidad * ol.PrecioUnit) - SUM(ols.ComisiónMonto) AS NetToSeller
FROM OrderLineSeller ols
JOIN Sellers s ON ols.SellerID = s.SellerID
JOIN OrderLines ol ON ols.LineID = ol.LineID
GROUP BY s.SellerID, s.Nombre;
Reportes & KPIs recomendados
KPI's esenciales
- GMV (Gross Merchandise Value): suma de ventas brutas por periodo.
- Revenue neto: GMV - reembolsos - comisiones - impuestos según necesidad.
- Top SKUs, Top Sellers.
- Tasa de conversión: sesiones → pedidos (requiere datos de eventos).
- Tiempo medio de despacho, Tasa de devoluciones.
Reportes prácticos
- Libro de ventas por día (por tienda/seller), exportable a CSV/Excel.
- Inventario por depósito con alertas de reabastecimiento.
- Listado de pedidos con pagos pendientes para conciliación.
- Dashboard de marketplace: ventas, comisiones, órdenes abiertas por vendedor.
Procesos reales y flujo de datos
Checkout simplificado (pasos y tablas involucradas)
- Carrito → se crea
Orders con OrderLines (reservar stock: StockMov RESERVA)
- Aplicar cupones → crear registro aplicado en OrderCoupons
- Iniciar pago → crear
Payments con estado "Iniciado"
- Confirmación de pago → actualizar Payments (Recibido) y Orders (Pagado); convertir reservas a SALIDA en
StockMovements
- Generar
Shipments y registrar Tracking; actualizar Order estado a Enviado/En Ruta
- Entrega confirmada → Order: Entregado; crear evento de auditoría
- En caso de devolución → crear StockMov DEVOLUCIÓN y Payment reverso (Refund)
Recomendaciones para implementación
- Usar transacciones (DB) para garantizar consistencia: reservar stock + crear pedido + iniciar pago
atomically.
- Diseñar conciliación bancaria: reconciliar Payments con extractos bancarios.
- Auditar todos los cambios críticos (price changes, cancellations) con
AuditLogs.
- Separar servicios: catálogo, pagos, stock y fulfillment como microservicios si escala requerida.
