Database Setup - Pro Stock Tool

Database Configuration

Pro Stock Tool uses MySQL/MariaDB as its database system. The database is named prostocktool and connects via the MySQLi extension.

Connection Settings

Default Configuration

The database connection is configured in /database/conexion.php:

<?php

$host = "localhost";
$user = "root";
$pass = "";
$db = "prostocktool";

$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_errno) {
    http_response_code(500);
    echo json_encode(["error" => "Error de conexión a la base de datos"]);
    exit;
}
?>

Environment Variables

For production deployments, override these values using environment variables:

Variable	Default	Description
`DB_HOST`	localhost	Database server hostname
`DB_USER`	root	Database username
`DB_PASSWORD`	(empty)	Database password
`DB_NAME`	prostocktool	Database name

Connection Properties

Extension: MySQLi (MySQL Improved)
Character Set: UTF-8 for proper Spanish character support
Error Handling: Connection failures return HTTP 500 with JSON error

Database Creation

Create Database

CREATE DATABASE prostocktool 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

Use Database

USE prostocktool;

Table Creation Scripts

usuarios Table

CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    identidad VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_identidad (identidad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

categorias Table

CREATE TABLE categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion VARCHAR(200),
    color VARCHAR(7) DEFAULT '#2e6df6',
    estado VARCHAR(20) DEFAULT 'ACTIVO',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

subcategorias Table

CREATE TABLE subcategorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    descripcion VARCHAR(200),
    color VARCHAR(7) DEFAULT '#2e6df6',
    estado VARCHAR(20) DEFAULT 'ACTIVO',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_nombre_categoria (nombre, categoria_id),
    INDEX idx_categoria_id (categoria_id),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

parametros Table

CREATE TABLE parametros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(10) NOT NULL UNIQUE,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion TEXT,
    color VARCHAR(7) DEFAULT '#4a90e2',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_codigo (codigo),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

proveedores Table

CREATE TABLE proveedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nif VARCHAR(20),
    nombre VARCHAR(100) NOT NULL,
    contacto VARCHAR(100),
    email VARCHAR(100),
    telefono VARCHAR(20),
    direccion VARCHAR(255),
    ciudad VARCHAR(100),
    web VARCHAR(255),
    terminos INT,
    parametro_id INT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parametro_id) REFERENCES parametros(id) ON DELETE RESTRICT,
    INDEX idx_nombre (nombre),
    INDEX idx_parametro_id (parametro_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

bodegas Table

CREATE TABLE bodegas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    descripcion TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

productos Table (Skeleton)

CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT,
    subcategoria_id INT,
    parametro_id INT,
    bodega_id INT,
    -- Additional product fields here
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE RESTRICT,
    FOREIGN KEY (subcategoria_id) REFERENCES subcategorias(id) ON DELETE RESTRICT,
    FOREIGN KEY (parametro_id) REFERENCES parametros(id) ON DELETE RESTRICT,
    FOREIGN KEY (bodega_id) REFERENCES bodegas(id) ON DELETE RESTRICT,
    INDEX idx_categoria_id (categoria_id),
    INDEX idx_subcategoria_id (subcategoria_id),
    INDEX idx_parametro_id (parametro_id),
    INDEX idx_bodega_id (bodega_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Complete Setup Script

Run this script to create the entire database structure:

-- Create database
CREATE DATABASE IF NOT EXISTS prostocktool 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE prostocktool;

-- Create tables in dependency order

-- 1. Independent tables (no foreign keys)
CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    identidad VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_identidad (identidad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion VARCHAR(200),
    color VARCHAR(7) DEFAULT '#2e6df6',
    estado VARCHAR(20) DEFAULT 'ACTIVO',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parametros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(10) NOT NULL UNIQUE,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion TEXT,
    color VARCHAR(7) DEFAULT '#4a90e2',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_codigo (codigo),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bodegas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    descripcion TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tables with one level of foreign keys
CREATE TABLE subcategorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    descripcion VARCHAR(200),
    color VARCHAR(7) DEFAULT '#2e6df6',
    estado VARCHAR(20) DEFAULT 'ACTIVO',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_nombre_categoria (nombre, categoria_id),
    INDEX idx_categoria_id (categoria_id),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE proveedores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nif VARCHAR(20),
    nombre VARCHAR(100) NOT NULL,
    contacto VARCHAR(100),
    email VARCHAR(100),
    telefono VARCHAR(20),
    direccion VARCHAR(255),
    ciudad VARCHAR(100),
    web VARCHAR(255),
    terminos INT,
    parametro_id INT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parametro_id) REFERENCES parametros(id) ON DELETE RESTRICT,
    INDEX idx_nombre (nombre),
    INDEX idx_parametro_id (parametro_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Products table (references multiple tables)
CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT,
    subcategoria_id INT,
    parametro_id INT,
    bodega_id INT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE RESTRICT,
    FOREIGN KEY (subcategoria_id) REFERENCES subcategorias(id) ON DELETE RESTRICT,
    FOREIGN KEY (parametro_id) REFERENCES parametros(id) ON DELETE RESTRICT,
    FOREIGN KEY (bodega_id) REFERENCES bodegas(id) ON DELETE RESTRICT,
    INDEX idx_categoria_id (categoria_id),
    INDEX idx_subcategoria_id (subcategoria_id),
    INDEX idx_parametro_id (parametro_id),
    INDEX idx_bodega_id (bodega_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Initial Data Setup

Default Parameters

Create initial product status parameters:

INSERT INTO parametros (codigo, nombre, descripcion, color) VALUES
('001', 'Disponible', 'Producto disponible en stock', '#4caf50'),
('002', 'Agotado', 'Producto sin stock', '#f44336'),
('003', 'En camino', 'Producto en tránsito', '#ff9800'),
('004', 'Descontinuado', 'Producto ya no disponible', '#9e9e9e');

Default Warehouse

Create a default warehouse:

INSERT INTO bodegas (nombre, descripcion) VALUES
('Bodega Principal', 'Almacén central de productos');

Sample Categories

Create sample product categories:

INSERT INTO categorias (nombre, descripcion, color, estado) VALUES
('Electrónica', 'Productos electrónicos y tecnología', '#2196f3', 'ACTIVO'),
('Ropa', 'Prendas de vestir y accesorios', '#e91e63', 'ACTIVO'),
('Alimentos', 'Productos alimenticios', '#4caf50', 'ACTIVO');

Security Configuration

Password Security

User passwords are hashed using bcrypt:

$hash = password_hash($contrasena, PASSWORD_BCRYPT);

To verify passwords during login:

password_verify($input_password, $stored_hash);

SQL Injection Prevention

All user inputs are escaped using MySQLi:

$nombre = $conn->real_escape_string($nombre);
$email = $conn->real_escape_string($email);

Prepared Statements (Recommended)

For enhanced security, consider migrating to prepared statements:

$stmt = $conn->prepare("INSERT INTO categorias (nombre, descripcion) VALUES (?, ?)");
$stmt->bind_param("ss", $nombre, $descripcion);
$stmt->execute();

CORS Configuration

All API endpoints include CORS headers:

header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

Cache Control

Dynamic data endpoints use no-cache headers:

header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Pragma: no-cache');

Database Maintenance

Backup Database

mysqldump -u root -p prostocktool > backup_$(date +%Y%m%d).sql

Restore Database

mysql -u root -p prostocktool < backup_20260303.sql

Optimize Tables

OPTIMIZE TABLE categorias, subcategorias, parametros, proveedores, bodegas, productos, usuarios;

Check Table Integrity

CHECK TABLE categorias, subcategorias, parametros, proveedores, bodegas, productos, usuarios;

Connection Pooling

For high-traffic applications, consider implementing connection pooling:

Use persistent connections:

$conn = new mysqli('p:' . $host, $user, $pass, $db);

Implement connection limits in MySQL:
```
SET GLOBAL max_connections = 200;
```

Monitoring

Check Active Connections

SHOW PROCESSLIST;

Monitor Table Sizes

SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'prostocktool'
ORDER BY (data_length + index_length) DESC;

Check Foreign Key Constraints

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'prostocktool';

Troubleshooting

Connection Failed

Error: “Error de conexión a la base de datos” Solutions:

Verify MySQL service is running:
```
systemctl status mysql
```
Check credentials in conexion.php
Verify database exists:
```
SHOW DATABASES LIKE 'prostocktool';
```

Foreign Key Constraint Fails

Error: Cannot add or update a child row Solutions:

Ensure referenced record exists in parent table
Check that foreign key column types match
Verify data type compatibility

Character Encoding Issues

Error: Spanish characters display incorrectly Solutions:

Set connection charset:
```
$conn->set_charset("utf8mb4");
```
Verify table charset:
```
SHOW CREATE TABLE categorias;
```

Production Recommendations

The default configuration uses empty password for root user. Always set strong passwords in production.

Use Environment Variables:

$host = getenv('DB_HOST') ?: 'localhost';
$user = getenv('DB_USER') ?: 'root';
$pass = getenv('DB_PASSWORD') ?: '';
$db = getenv('DB_NAME') ?: 'prostocktool';

Enable SSL Connections:

$conn->ssl_set(null, null, "/path/to/ca.pem", null, null);

Limit User Privileges:

CREATE USER 'prostockapp'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON prostocktool.* TO 'prostockapp'@'localhost';

Enable Query Logging (for debugging):

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

Configure Backups: Set up automated daily backups with retention policy

Endpoints

Database

​Database Configuration

​Connection Settings

​Default Configuration

​Environment Variables

​Connection Properties

​Database Creation

​Create Database

​Use Database

​Table Creation Scripts

​usuarios Table

​categorias Table

​subcategorias Table

​parametros Table

​proveedores Table

​bodegas Table

​productos Table (Skeleton)

​Complete Setup Script

​Initial Data Setup

​Default Parameters

​Default Warehouse

​Sample Categories

​Security Configuration

​Password Security

​SQL Injection Prevention

​Prepared Statements (Recommended)

​CORS Configuration

​Cache Control

​Database Maintenance

​Backup Database

​Restore Database

​Optimize Tables

​Check Table Integrity

​Connection Pooling

​Monitoring

​Check Active Connections

​Monitor Table Sizes

​Check Foreign Key Constraints

​Troubleshooting

​Connection Failed

​Foreign Key Constraint Fails

​Character Encoding Issues

​Production Recommendations

Build docs developers (and LLMs) love

Database Configuration

Connection Settings

Default Configuration

Environment Variables

Connection Properties

Database Creation

Create Database

Use Database

Table Creation Scripts

usuarios Table

categorias Table

subcategorias Table

parametros Table

proveedores Table

bodegas Table

productos Table (Skeleton)

Complete Setup Script

Initial Data Setup

Default Parameters

Default Warehouse

Sample Categories

Security Configuration

Password Security

SQL Injection Prevention

Prepared Statements (Recommended)

CORS Configuration

Cache Control

Database Maintenance

Backup Database

Restore Database

Optimize Tables

Check Table Integrity

Connection Pooling

Monitoring

Check Active Connections

Monitor Table Sizes

Check Foreign Key Constraints

Troubleshooting

Connection Failed

Foreign Key Constraint Fails

Character Encoding Issues

Production Recommendations