Database Schema Overview

​

Database Architecture

​

Entity Relationship Overview

​

Core Domains

1. Product Management
   • prenda (garment/item) - Central product catalog
   • categoria - Product categories
   • talla - Size variants
   • color - Color options
2. Operations & Inventory
   • movimiento_stock - Stock movements (entries, exits, adjustments)
   • actualizacion - Price update history
3. Business Relationships
   • empleado - Employee records
   • proveedor - Supplier information
   • registro - Product registration linking items to suppliers

​

Tables Summary

​

Key Relationships

​

Primary Foreign Key Relationships

​

Relationship Details

​

Design Philosophy

​

Normalization

• Product attributes (category, size, color) are separated into lookup tables
• Transactional data (movements, updates) maintains historical integrity
• No redundant data storage except for denormalized audit fields

​

Audit Trail

• movimiento_stock.fecha - Timestamp of inventory changes
• actualizacion.precio_anterior / precio_nuevo - Price change history
• registro.fecha_registro - Product registration timestamps
• All transactional tables reference id_empleado for accountability

​

Indexing Strategy

• Primary Keys: Auto-increment integers on all tables
• Foreign Key Indexes: Automatic indexes on all FK columns
• Search Indexes: idx_prenda_nombre for product name searches
• Composite Indexes: idx_prenda_cat_talla for filtered queries
• Date Indexes: idx_movimiento_fecha for temporal queries

​

Data Integrity

• Foreign key constraints enforce referential integrity
• NOT NULL constraints on critical fields
• DEFAULT values for timestamps (CURRENT_TIMESTAMP)
• UNIQUE constraint on proveedor.telefono
• Decimal precision for monetary values (10,2)

​

Common Query Patterns

​

Inventory Status

SELECT p.nombre, p.stock_actual, c.nombre as categoria
FROM prenda p
INNER JOIN categoria c ON p.id_categoria = c.id_categoria
WHERE p.stock_actual < 20
ORDER BY p.stock_actual ASC;

​

Price History

SELECT p.nombre, a.fecha, a.precio_anterior, a.precio_nuevo,
       (a.precio_nuevo - a.precio_anterior) as cambio,
       e.nombre as empleado
FROM actualizacion a
INNER JOIN prenda p ON a.id_prenda = p.id_prenda
INNER JOIN empleado e ON a.id_empleado = e.id_empleado
ORDER BY a.fecha DESC;

​

Stock Movements by Employee

SELECT e.nombre, COUNT(*) as total_movimientos,
       SUM(CASE WHEN m.tipo_movimiento = 'entrada' THEN m.cantidad ELSE 0 END) as entradas,
       SUM(CASE WHEN m.tipo_movimiento = 'salida' THEN m.cantidad ELSE 0 END) as salidas
FROM movimiento_stock m
INNER JOIN empleado e ON m.id_empleado = e.id_empleado
GROUP BY e.id_empleado, e.nombre;

​

Database Statistics

• 5 Categories: Caballero, Dama, Infantil, Deportiva, Accesorios
• 5 Employees: Mix of gerentes and empleados
• 5 Suppliers: Local Aguascalientes providers
• 20+ Products: Various clothing items across categories
• 20+ Stock Movements: Tracking inventory changes since October 2025
• 6 Price Updates: Historical price adjustments
• 6 Registration Records: Product-supplier relationships