Database Configuration

Overview

Sistema de Ventas uses MySQL as its primary database engine. This guide covers database setup, schema structure, connection configuration, and migrations.

Database Requirements

Sistema de Ventas requires MySQL 8.0 or higher with support for utf8mb4 character encoding.

Database Engine: MySQL 8.0+
Character Set: utf8mb4
Collation: utf8mb4_general_ci
PHP Extension: PDO MySQL driver

Database Setup

Step 1: Create Database

Create the database

Connect to your MySQL server and create a new database:

CREATE DATABASE tutorial_sistema_ventas CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Import the schema

Import the provided SQL file to create all tables with sample data:

mysql -u root -p tutorial_sistema_ventas < BD/tutorial_sistema_ventas.sql

Verify installation

Check that all tables were created successfully:

USE tutorial_sistema_ventas;
SHOW TABLES;

You should see 10 tables: categoria, cliente, empresa, entrada, producto, proveedor, tipo_usuario, usuario, venta, and venta_detalle.

Database Schema

The system uses a relational database schema with the following core tables:

Core Tables

usuario (Users)

Stores system user accounts with role-based access.

CREATE TABLE `usuario` (
  `id_usuario` int NOT NULL AUTO_INCREMENT,
  `tipo_usuario` int NOT NULL,
  `nombre` varchar(100) DEFAULT NULL,
  `apellido` varchar(100) DEFAULT NULL,
  `usuario` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `telefono` varchar(20) DEFAULT NULL,
  `direccion` varchar(100) DEFAULT NULL,
  `correo` varchar(100) NOT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `estado` tinyint NOT NULL,
  PRIMARY KEY (`id_usuario`),
  KEY `fk1` (`tipo_usuario`),
  CONSTRAINT `fk1` FOREIGN KEY (`tipo_usuario`) 
    REFERENCES `tipo_usuario` (`id_tipo`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Key Fields:

usuario: Username field (used for login instead of email)
password: Bcrypt hashed password
tipo_usuario: Foreign key to user roles (1=administrador, 2=empleado)
estado: User status (1=active, 0=inactive)

tipo_usuario (User Types)

Defines user roles and permissions.

CREATE TABLE `tipo_usuario` (
  `id_tipo` int NOT NULL AUTO_INCREMENT,
  `tipo` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id_tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Default Roles:

1: administrador (Administrator)
2: empleado (Employee)

categoria (Categories)

Product category management.

CREATE TABLE `categoria` (
  `id_categoria` int NOT NULL AUTO_INCREMENT,
  `nombre` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id_categoria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

producto (Products)

Product catalog with inventory management.

CREATE TABLE `producto` (
  `id_producto` int NOT NULL AUTO_INCREMENT,
  `id_categoria` int NOT NULL,
  `codigo` varchar(255) DEFAULT NULL,
  `nombre` varchar(50) NOT NULL,
  `precio` decimal(10,2) NOT NULL,
  `stock` int NOT NULL,
  `descripcion` varchar(255) DEFAULT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `estado` tinyint NOT NULL,
  PRIMARY KEY (`id_producto`),
  KEY `fk5` (`id_categoria`),
  CONSTRAINT `fk5` FOREIGN KEY (`id_categoria`) 
    REFERENCES `categoria` (`id_categoria`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

cliente (Customers)

Customer information management.

CREATE TABLE `cliente` (
  `id_cliente` int NOT NULL AUTO_INCREMENT,
  `dni` varchar(50) DEFAULT NULL,
  `nombre` varchar(100) DEFAULT '',
  `apellido` varchar(100) DEFAULT NULL,
  `telefono` varchar(20) DEFAULT NULL,
  `direccion` varchar(255) DEFAULT NULL,
  `correo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_cliente`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

proveedor (Suppliers)

Supplier management for inventory.

CREATE TABLE `proveedor` (
  `id_proveedor` int NOT NULL AUTO_INCREMENT,
  `nombre` varchar(100) DEFAULT NULL,
  `apellido` varchar(100) DEFAULT NULL,
  `dni` varchar(50) DEFAULT NULL,
  `telefono` varchar(20) DEFAULT NULL,
  `direccion` varchar(100) DEFAULT NULL,
  `ruc` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id_proveedor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

empresa (Company)

Company profile and configuration.

CREATE TABLE `empresa` (
  `id_empresa` int NOT NULL AUTO_INCREMENT,
  `nombre` varchar(255) DEFAULT NULL,
  `ubicacion` varchar(255) DEFAULT NULL,
  `telefono` varchar(20) DEFAULT '',
  `ruc` varchar(50) DEFAULT NULL,
  `correo` varchar(100) DEFAULT NULL,
  `foto` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_empresa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Transaction Tables

venta (Sales)

Sales transaction headers.

CREATE TABLE `venta` (
  `id_venta` int NOT NULL AUTO_INCREMENT,
  `id_cliente` int NOT NULL,
  `id_usuario` int NOT NULL,
  `fecha` datetime NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `descuento` decimal(10,2) DEFAULT '0.00',
  `pagoTotal` decimal(10,2) DEFAULT NULL,
  `estado` tinyint DEFAULT NULL,
  PRIMARY KEY (`id_venta`),
  KEY `fk3` (`id_cliente`),
  KEY `fk4` (`id_usuario`),
  CONSTRAINT `fk3` FOREIGN KEY (`id_cliente`) 
    REFERENCES `cliente` (`id_cliente`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk4` FOREIGN KEY (`id_usuario`) 
    REFERENCES `usuario` (`id_usuario`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

venta_detalle (Sales Details)

Line items for each sale.

CREATE TABLE `venta_detalle` (
  `id_venta_detalle` int NOT NULL AUTO_INCREMENT,
  `id_venta` int NOT NULL,
  `id_producto` int NOT NULL,
  `precio` decimal(10,2) DEFAULT NULL,
  `cantidad` int DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  `estado` tinyint DEFAULT NULL,
  PRIMARY KEY (`id_venta_detalle`),
  KEY `fk2` (`id_producto`),
  KEY `venta` (`id_venta`),
  CONSTRAINT `fk2` FOREIGN KEY (`id_producto`) 
    REFERENCES `producto` (`id_producto`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `venta` FOREIGN KEY (`id_venta`) 
    REFERENCES `venta` (`id_venta`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

entrada (Stock Entries)

Inventory receiving transactions.

CREATE TABLE `entrada` (
  `id_entrada` int NOT NULL AUTO_INCREMENT,
  `id_producto` int NOT NULL,
  `id_proveedor` int DEFAULT NULL,
  `cantidad` int NOT NULL,
  `precio` decimal(10,2) NOT NULL,
  `fecha` datetime DEFAULT NULL,
  PRIMARY KEY (`id_entrada`),
  KEY `fk7` (`id_producto`),
  KEY `fk8` (`id_proveedor`),
  CONSTRAINT `fk7` FOREIGN KEY (`id_producto`) 
    REFERENCES `producto` (`id_producto`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk8` FOREIGN KEY (`id_proveedor`) 
    REFERENCES `proveedor` (`id_proveedor`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Connection Configuration

Environment Variables

Configure your database connection in the .env file:

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=sisreservacita
DB_USERNAME=root
DB_PASSWORD=

The default .env.example shows DB_DATABASE=sisreservacita, but the actual database name should be tutorial_sistema_ventas based on the SQL schema.

Configuration File

The database configuration is located at config/database.php:

config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => false,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

Key Settings:

strict is set to false to allow more flexible SQL queries
collation uses utf8mb4_unicode_ci by default
SSL support is available via MYSQL_ATTR_SSL_CA environment variable

Testing Connection

Test your database connection using Artisan:

php artisan db:show

Or run a simple query:

php artisan tinker
>>> DB::connection()->getPdo();

Migrations

Available Migrations

The system includes these Laravel migrations:

// database/migrations/2014_10_12_000000_create_users_table.php
// Default Laravel users table (not used by the system)

The main application tables are created via the SQL import file rather than Laravel migrations. The migration files are primarily for Laravel’s built-in authentication features.

Running Migrations

php artisan migrate

Rolling Back Migrations

php artisan migrate:rollback

Troubleshooting

Connection Refused

If you see “Connection refused” errors:

Verify MySQL is running:
```
sudo systemctl status mysql
```
Check the host and port in .env
Verify user credentials

Access Denied

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

Solution: Grant proper permissions:

GRANT ALL PRIVILEGES ON tutorial_sistema_ventas.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

Character Encoding Issues

If you see garbled Spanish characters:

Ensure database charset is utf8mb4
Verify collation is utf8mb4_general_ci
Check your MySQL client encoding:
```
SHOW VARIABLES LIKE 'character_set%';
```

Foreign Key Constraints

All tables use cascading deletes and updates. Be careful when deleting records:

Deleting a categoria will cascade delete all associated producto records, which will also cascade to venta_detalle and entrada records.

Best Practices

Backups: Regularly backup your database:

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

Indexes: The schema includes proper indexes on foreign keys for optimal query performance
Transactions: Use database transactions for critical operations like sales processing
Connection Pooling: Consider using persistent connections in production

Get Started

Core Features

Configuration

Overview

Database Requirements

Database Setup

Step 1: Create Database

Database Schema

Core Tables

usuario (Users)

tipo_usuario (User Types)

categoria (Categories)

producto (Products)

cliente (Customers)

proveedor (Suppliers)

empresa (Company)

Transaction Tables

venta (Sales)

venta_detalle (Sales Details)

entrada (Stock Entries)

Connection Configuration

Environment Variables

Configuration File

Testing Connection

Migrations

Available Migrations

Running Migrations

Rolling Back Migrations

Troubleshooting

Connection Refused

Access Denied

Character Encoding Issues

Foreign Key Constraints

Best Practices

Next Steps

Environment Configuration

Authentication Setup

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration

​Overview

​Database Requirements

​Database Setup

​Step 1: Create Database

​Database Schema

​Core Tables

​usuario (Users)

​tipo_usuario (User Types)

​categoria (Categories)

​producto (Products)

​cliente (Customers)

​proveedor (Suppliers)

​empresa (Company)

​Transaction Tables

​venta (Sales)

​venta_detalle (Sales Details)

​entrada (Stock Entries)

​Connection Configuration

​Environment Variables

​Configuration File

​Testing Connection

​Migrations

​Available Migrations

​Running Migrations

​Rolling Back Migrations

​Troubleshooting

​Connection Refused

​Access Denied

​Character Encoding Issues

​Foreign Key Constraints

​Best Practices

​Next Steps

Environment Configuration

Authentication Setup

Build docs developers (and LLMs) love

Overview

Database Requirements

Database Setup

Step 1: Create Database

Database Schema

Core Tables

usuario (Users)

tipo_usuario (User Types)

categoria (Categories)

producto (Products)

cliente (Customers)

proveedor (Suppliers)

empresa (Company)

Transaction Tables

venta (Sales)

venta_detalle (Sales Details)

entrada (Stock Entries)

Connection Configuration

Environment Variables

Configuration File

Testing Connection

Migrations

Available Migrations

Running Migrations

Rolling Back Migrations

Troubleshooting

Connection Refused

Access Denied

Character Encoding Issues

Foreign Key Constraints

Best Practices

Next Steps