SQL Schemas - Documentation

Overview

This section contains database structure definitions and schema documentation for the Procesar system.

Available Schema Categories

Core Tables

• Users: User account and profile information
• Accounts: Financial account data and balances
• Transactions: Transaction records and history
• Audit: System access and operation logs

Reference Tables

• Countries: Country codes and names
• States: State/province codes and names
• Transaction Types: Transaction classification and codes
• Account Types: Account categories and configurations

Schema Examples

Users Table

CREATE TABLE usuarios (
  id SERIAL PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  apellido_paterno VARCHAR(100) NOT NULL,
  apellido_materno VARCHAR(100),
  curp VARCHAR(18) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  telefono VARCHAR(20),
  fecha_nacimiento DATE,
  fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  estatus INTEGER DEFAULT 1,
  CONSTRAINT chk_curp_formato CHECK (curp ~ '^[A-Z]{4}[0-9]{6}[HM][A-Z]{5}[0-2][A-Z][0-9]$')
);

CREATE INDEX idx_usuarios_curp ON usuarios(curp);
CREATE INDEX idx_usuarios_email ON usuarios(email);

Accounts Table

CREATE TABLE cuentas (
  id SERIAL PRIMARY KEY,
  id_usuario INTEGER REFERENCES usuarios(id),
  tipo_cuenta VARCHAR(50) NOT NULL,
  saldo DECIMAL(15,2) DEFAULT 0.00,
  fecha_apertura TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  estatus INTEGER DEFAULT 1,
  fecha_ultimo_movimiento TIMESTAMP
);

CREATE INDEX idx_cuentas_usuario ON cuentas(id_usuario);
CREATE INDEX idx_cuentas_tipo ON cuentas(tipo_cuenta);

Transactions Table

CREATE TABLE transacciones (
  id SERIAL PRIMARY KEY,
  id_cuenta INTEGER REFERENCES cuentas(id),
  id_usuario INTEGER REFERENCES usuarios(id),
  monto DECIMAL(15,2) NOT NULL,
  tipo_transaccion VARCHAR(10) NOT NULL,
  descripcion VARCHAR(255),
  fecha_transaccion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  estatus INTEGER DEFAULT 1
);

CREATE INDEX idx_transacciones_cuenta ON transacciones(id_cuenta);
CREATE INDEX idx_transacciones_usuario ON transacciones(id_usuario);
CREATE INDEX idx_transacciones_fecha ON transacciones(fecha_transaccion);

Schema Documentation

Table Relationships

Data Types

Type	Description	Example
SERIAL	Auto-incrementing integer	1, 2, 3...
VARCHAR	Variable-length string	VARCHAR(100)
DECIMAL	Fixed-point number	DECIMAL(15,2)
TIMESTAMP	Date and time	2023-10-25 14:30:00
INTEGER	Whole number	1, 2, 3...

Implementation Guidelines

For Developers

1. Normalization: Follow database normalization principles
2. Indexing Strategy: Create appropriate indexes for query performance
3. Data Integrity: Implement foreign key constraints and validation rules
4. Migration Strategy: Plan for schema changes and data migration

For Database Administrators

1. Regular Backups: Schedule automated backups of all schemas
2. Version Control: Track all schema changes with migration scripts
3. Performance Monitoring: Monitor query performance and optimize as needed
4. Documentation: Maintain complete documentation for all schemas

Support

For SQL schema issues:

• Database Team: Contact database administrators
• Development Team: Consult with application developers
• Documentation: Review specific schema examples and guidelines