Database Design - Sistema de Ventas

Database Architecture

Sistema de Ventas uses a database-per-service pattern, where each microservice has its own isolated database schema. This approach provides:

Data Independence: Services own their data
Technology Diversity: Mix of MySQL and PostgreSQL
Independent Scaling: Scale databases independently
Loose Coupling: No direct database dependencies between services

Database Distribution

MySQL Databases (6)

auth-jea (Authentication)
catalogo-ms (Products & Categories)
venta-jea (Sales)
compra-jea (Purchases)
pedido-jea (Orders)
pagos-ms (Payment Methods)

PostgreSQL Databases (2)

cliente-jea (Customers)
proveedor-jea (Suppliers)

Database Initialization

SQL Scripts

The system includes initialization scripts in sql-scripts/:

01-init-databases.sql

Creates all required databases:

-- Create databases with UTF-8 encoding
CREATE DATABASE IF NOT EXISTS jeaauth CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeacatalogo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeacliente CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jesinventario CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeaventa CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeacompra CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeapagos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeaproveedor CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note the database naming convention:

Config files use: auth-jea, catalogo-ms, venta-jea, etc.
Init script uses: jeaauth, jeacatalogo, jeaventa, etc.

Ensure consistency between configuration and initialization scripts.

02-datos-ejemplo.sql

Populates databases with sample data: Proveedores (Suppliers)

USE jeaproveedor;

INSERT INTO proveedor (ruc, nombre, telefono, direccion, correo, estado) VALUES
('20123456789', 'Distribuidora Los Andes SAC', '987654321', 'Av. Los Incas 123, Lima', '[email protected]', true),
('20987654321', 'Comercial San Martín EIRL', '912345678', 'Jr. San Martín 456, Arequipa', '[email protected]', true),
('20555666777', 'Importadora del Norte SRL', '998877665', 'Av. América Norte 789, Trujillo', '[email protected]', true);

Formas de Pago (Payment Methods)

USE jeapagos;

INSERT INTO forma_pago (nombre) VALUES
('Efectivo'),
('Transferencia Bancaria'),
('Cheque'),
('Tarjeta de Crédito'),
('Tarjeta de Débito'),
('Depósito Bancario'),
('Letra de Cambio'),
('Pagaré');

Categorías y Productos (Categories & Products)

USE jeacatalogo;

INSERT INTO categoria (nombre, descripcion, estado) VALUES
('Electrónicos', 'Productos electrónicos y tecnológicos', true),
('Oficina', 'Artículos de oficina y papelería', true),
('Hogar', 'Productos para el hogar', true),
('Construcción', 'Materiales de construcción', true),
('Alimentación', 'Productos alimenticios', true);

INSERT INTO producto (codigo, nombre, descripcion, costo_compra, precio_venta, cantidad, categoria_id, estado, fecha_creacion) VALUES
('PROD001', 'Laptop HP Pavilion', 'Laptop HP Pavilion 15.6 pulgadas', 2500.00, 3200.00, 10, 1, true, NOW()),
('PROD002', 'Mouse Inalámbrico Logitech', 'Mouse inalámbrico con receptor USB', 45.00, 75.00, 50, 1, true, NOW()),
('PROD003', 'Papel Bond A4', 'Resma de papel bond A4 500 hojas', 12.00, 18.00, 100, 2, true, NOW());

Usuarios (Users)

USE jeaauth;

-- Password: admin123 (BCrypt hashed)
INSERT INTO usuario (username, password, email, activo) VALUES
('admin', '$2a$10$N9qo8uLOickgx2ZMRZoMye0vVwOUFHLmM9jrLY7Dm3U5QFNuSTU1K', '[email protected]', true),
('vendedor', '$2a$10$N9qo8uLOickgx2ZMRZoMye0vVwOUFHLmM9jrLY7Dm3U5QFNuSTU1K', '[email protected]', true),
('compras', '$2a$10$N9qo8uLOickgx2ZMRZoMye0vVwOUFHLmM9jrLY7Dm3U5QFNuSTU1K', '[email protected]', true);

03-verificar-datos.sql

Verifies data insertion:

SELECT 
    (SELECT COUNT(*) FROM jeaproveedor.proveedor WHERE estado = true) as proveedores_activos,
    (SELECT COUNT(*) FROM jeapagos.forma_pago) as formas_pago,
    (SELECT COUNT(*) FROM jeacatalogo.categoria WHERE estado = true) as categorias_activas,
    (SELECT COUNT(*) FROM jeacatalogo.producto WHERE estado = true) as productos_activos,
    (SELECT COUNT(*) FROM jeaauth.usuario WHERE activo = true) as usuarios_activos;

Schema Management

JPA Auto-DDL

All services use JPA’s automatic schema generation:

spring:
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

DDL-Auto Options:

update: Updates schema without dropping data
create: Drops and recreates schema on startup
create-drop: Creates schema, drops on shutdown
validate: Validates schema without changes
none: No automatic schema management

For production, use validate or none and manage schema via migration tools (Flyway/Liquibase).

Database Connections

MySQL Configuration
PostgreSQL Configuration

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/database-name
    username: root
    password: ""
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/database-name
    username: postgres
    password: 123456
    driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect

Database Schemas by Service

Auth Database (`auth-jea`)

Tables:

auth_user: Authentication credentials
usuario: User profile information
rol: User roles
acceso: Permissions/Access rights
usuario_rol: User-Role mapping (Many-to-Many)
acceso_rol: Role-Access mapping (Many-to-Many)

Key Relationships:

usuario (1) ←→ (1) auth_user
usuario (N) ←→ (M) rol
rol (N) ←→ (M) acceso

Catalogo Database (`catalogo-ms`)

Tables:

categoria: Product categories
producto: Products with inventory

Key Relationships:

categoria (1) ←→ (N) producto

Business Logic:

Unique product codes and names
Automatic timestamp management
Default quantity: 0
Image storage path reference

Cliente Database (`cliente-jea`)

Tables:

cliente: Customer information

Unique Constraints:

dni (Document ID)
email

Default Values:

activo: true
fecha_registro: Current timestamp

Venta Database (`venta-jea`)

Tables:

venta: Sales header
venta_detalle: Sales line items

Key Relationships:

venta (1) ←→ (N) venta_detalle

Foreign Key References (not enforced):

cliente_id → Cliente Service
producto_id → Catalogo Service
formapago_id → Pagos Service

Unique Constraints:

serie (3 letters, auto-generated)
numero (6 digits, auto-generated)

Calculated Fields:

base_imponible: Price / 1.18
igv: Price - base_imponible (18% tax)
total: Sum of all line items

Compra Database (`compra-jea`)

Tables:

compra: Purchase header
compra_detalle: Purchase line items

Key Relationships:

compra (1) ←→ (N) compra_detalle

Foreign Key References (not enforced):

proveedor_id → Proveedor Service
producto_id → Catalogo Service
formapago_id → Pagos Service

Similar Structure to Venta:

Auto-generated serie and numero
Tax calculations (18% IGV)
Automatic totals calculation

Pedido Database (`pedido-jea`)

Tables:

pedido: Order header
pedido_detalle: Order line items

Key Relationships:

pedido (1) ←→ (N) pedido_detalle

Foreign Key References (not enforced):

cliente_id → Cliente Service
producto_id → Catalogo Service
formapago_id → Pagos Service

Additional Fields:

estado: Order status (PENDIENTE, EN_PROCESO, COMPLETADO)
fecha_entrega: Delivery date (default: +7 days)
codigo: Order code
serie: Order series

Pagos Database (`pagos-ms`)

Tables:

forma_pago: Payment methods catalog

Simple Structure:

id: Auto-increment
nombre: Payment method name

No relationships - serves as reference data for other services

Proveedor Database (`proveedor-jea`)

Tables:

proveedor: Supplier information

Fields:

ruc: Tax ID (unique)
nombre: Company name
telefono: Phone number
direccion: Address
correo: Email
estado: Active status (default: true)

Cross-Service Data References

Transient Fields Pattern

Services use @Transient fields to hold data from other services:

@Entity
public class Venta {
    @Column(name = "cliente_id")
    private Long clienteId;           // Stored in database
    
    @Transient
    private Cliente cliente;          // Fetched via Feign, not stored
}

Data Consistency

No Foreign Key Constraints Across ServicesThe system uses ID references without database-level foreign keys. This means:

Application-level referential integrity
Services must validate references via Feign calls
Potential for orphaned references if not managed carefully
Need for eventual consistency patterns

Data Flow Example: Creating a Sale

Database Deployment

Docker Compose Example

services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: initial_db
    ports:
      - "3306:3306"
    volumes:
      - ./sql-scripts/01-init-databases.sql:/docker-entrypoint-initdb.d/01-init.sql
      - ./sql-scripts/02-datos-ejemplo.sql:/docker-entrypoint-initdb.d/02-data.sql
      - mysql-data:/var/lib/mysql
  
  postgres:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: 123456
      POSTGRES_USER: postgres
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data

volumes:
  mysql-data:
  postgres-data:

Environment-Specific Configuration

Development
Production

Use localhost connections
Empty or simple passwords
show-sql: true for debugging
ddl-auto: update

Use environment variables for credentials
Strong passwords via secrets management
show-sql: false
ddl-auto: validate or none
Connection pooling configured
SSL/TLS encryption enabled

Data Migration Strategy

Current Approach

JPA auto-generates schemas from entity classes
Manual SQL scripts for initial data
No version-controlled migrations

Recommended for Production

Implement Flyway or Liquibase

Add migration tool for version-controlled schema changes

Disable Auto-DDL

Set ddl-auto: validate or none

Create Baseline Migrations

Generate initial schema from existing entities

Version Control All Changes

All schema changes go through migration scripts

Flyway Configuration Example

spring:
  flyway:
    enabled: true
    baseline-on-migrate: true
    locations: classpath:db/migration
  jpa:
    hibernate:
      ddl-auto: validate

Database Monitoring

JPA Statistics

Enable Hibernate statistics for monitoring:

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        show_sql: true
        format_sql: true

Connection Pool Monitoring

Configure HikariCP (default pool):

spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

Actuator Endpoints

Monitor database health:

/actuator/health/db
/actuator/metrics/hikaricp.*
/actuator/metrics/jdbc.*

Backup and Recovery

MySQL Backup

# Backup all databases
mysqldump -u root -p --all-databases > backup.sql

# Backup specific database
mysqldump -u root -p auth-jea > auth-jea-backup.sql

PostgreSQL Backup

# Backup database
pg_dump -U postgres -d cliente-jea > cliente-jea-backup.sql

# Backup all databases
pg_dumpall -U postgres > all-databases-backup.sql

Automated Backup Strategy

Daily automated backups
Retention policy (e.g., 30 days)
Off-site backup storage
Regular restore testing
Point-in-time recovery capability

Performance Considerations

Indexing

Automatic Indexes (via JPA):

Primary keys (@Id)
Unique constraints (unique = true)
Foreign keys (@ManyToOne, @OneToOne)

Recommended Additional Indexes:

-- Venta queries by date
CREATE INDEX idx_venta_fecha ON venta(fecha_venta);

-- Product searches
CREATE INDEX idx_producto_nombre ON producto(nombre);
CREATE INDEX idx_producto_codigo ON producto(codigo);

-- Customer lookups
CREATE INDEX idx_cliente_dni ON cliente(dni);
CREATE INDEX idx_cliente_email ON cliente(email);

Query Optimization

Use pagination for large result sets
Implement caching for reference data (categories, payment methods)
Use lazy loading judiciously
Consider read replicas for reporting

Database Scaling

Vertical Scaling

Increase database server resources
Optimize queries and indexes
Connection pool tuning

Horizontal Scaling

Read replicas for queries
Sharding by service
Cache layer (Redis)

Next Steps

Data Models

Explore entity relationships and domain models

Microservices

Review microservice implementations

Overview

Services

Infrastructure Components

Data Layer

​Database Architecture

​Database Distribution

MySQL Databases (6)

PostgreSQL Databases (2)

​Database Initialization

​SQL Scripts

​01-init-databases.sql

​02-datos-ejemplo.sql

​03-verificar-datos.sql

​Schema Management

​JPA Auto-DDL

​Database Connections

​Database Schemas by Service

​Auth Database (auth-jea)

​Catalogo Database (catalogo-ms)

​Cliente Database (cliente-jea)

​Venta Database (venta-jea)

​Compra Database (compra-jea)

​Pedido Database (pedido-jea)

​Pagos Database (pagos-ms)

​Proveedor Database (proveedor-jea)

​Cross-Service Data References

​Transient Fields Pattern

​Data Consistency

​Data Flow Example: Creating a Sale

​Database Deployment

​Docker Compose Example

​Environment-Specific Configuration

​Data Migration Strategy

​Current Approach

​Recommended for Production

​Flyway Configuration Example

​Database Monitoring

​JPA Statistics

​Connection Pool Monitoring

​Actuator Endpoints

​Backup and Recovery

​MySQL Backup

​PostgreSQL Backup

​Automated Backup Strategy

​Performance Considerations

​Indexing

​Query Optimization

​Database Scaling

Vertical Scaling

Horizontal Scaling

​Next Steps

Data Models

Microservices

Build docs developers (and LLMs) love

Database Architecture

Database Distribution

Database Initialization

SQL Scripts

01-init-databases.sql

02-datos-ejemplo.sql

03-verificar-datos.sql

Schema Management

JPA Auto-DDL

Database Connections

Database Schemas by Service

Auth Database (`auth-jea`)

Catalogo Database (`catalogo-ms`)

Cliente Database (`cliente-jea`)

Venta Database (`venta-jea`)

Compra Database (`compra-jea`)

Pedido Database (`pedido-jea`)

Pagos Database (`pagos-ms`)

Proveedor Database (`proveedor-jea`)

Cross-Service Data References

Transient Fields Pattern

Data Consistency

Data Flow Example: Creating a Sale

Database Deployment

Docker Compose Example

Environment-Specific Configuration

Data Migration Strategy

Current Approach

Recommended for Production

Flyway Configuration Example

Database Monitoring

JPA Statistics

Connection Pool Monitoring

Actuator Endpoints

Backup and Recovery

MySQL Backup

PostgreSQL Backup

Automated Backup Strategy

Performance Considerations

Indexing

Query Optimization

Database Scaling

Next Steps