Overview
Mis Compras uses MySQL 8.4.3+ as its primary database management system. The platform requires a properly configured database with the correct schema, tables, and relationships to function.
Prerequisites
Ensure you have MySQL 8.4.3 or higher installed on your system before proceeding.
- MySQL Server 8.4.3+
- phpMyAdmin 5.2.2+ (recommended for GUI management)
- Access to MySQL command line or GUI client
- Proper user permissions to create databases and tables
Database Installation
Create the Database
Import the provided SQL schema file located at tienda_online (1).sql:mysql -u root -p < "tienda_online (1).sql"
Verify Database Creation
Connect to MySQL and verify the database exists:SHOW DATABASES;
USE tienda_online;
SHOW TABLES;
carrito, categorias, detalle_carrito, detalle_pedido, pedidos, productos, and usuarios. Configure User Permissions
Grant necessary permissions to your application user:GRANT ALL PRIVILEGES ON tienda_online.* TO 'root'@'localhost';
FLUSH PRIVILEGES;
Database Schema
Core Tables
The platform uses the following database structure:
usuarios (Users)
Stores user account information with password hashing.
CREATE TABLE `usuarios` (
`id_usuario` int NOT NULL AUTO_INCREMENT,
`nombre` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL UNIQUE,
`contrasena` varchar(255) NOT NULL,
`fecha_registro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id_usuario`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
productos (Products)
Contains product listings with vendor relationships.
CREATE TABLE `productos` (
`id_producto` int NOT NULL AUTO_INCREMENT,
`nombre` varchar(100) NOT NULL,
`descripcion` text,
`precio` decimal(10,2) NOT NULL,
`imagen` varchar(255) DEFAULT NULL,
`id_vendedor` int DEFAULT NULL,
`id_categoria` int DEFAULT NULL,
`fecha_publicacion` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id_producto`),
FOREIGN KEY (`id_vendedor`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE,
FOREIGN KEY (`id_categoria`) REFERENCES `categorias` (`id_categoria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
categorias (Categories)
Product categorization system.
CREATE TABLE `categorias` (
`id_categoria` int NOT NULL AUTO_INCREMENT,
`nombre` varchar(100) NOT NULL,
`descripcion` text,
PRIMARY KEY (`id_categoria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `carrito` (
`id_carrito` int NOT NULL AUTO_INCREMENT,
`id_usuario` int DEFAULT NULL,
`fecha_creacion` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id_carrito`),
FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
detalle_carrito (Cart Items)
Individual items within shopping carts.
CREATE TABLE `detalle_carrito` (
`id_detalle` int NOT NULL AUTO_INCREMENT,
`id_carrito` int DEFAULT NULL,
`id_producto` int DEFAULT NULL,
`cantidad` int DEFAULT 1,
PRIMARY KEY (`id_detalle`),
FOREIGN KEY (`id_carrito`) REFERENCES `carrito` (`id_carrito`) ON DELETE CASCADE,
FOREIGN KEY (`id_producto`) REFERENCES `productos` (`id_producto`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
pedidos (Orders)
Order tracking and management.
CREATE TABLE `pedidos` (
`id_pedido` int NOT NULL AUTO_INCREMENT,
`id_usuario` int DEFAULT NULL,
`total` decimal(10,2) NOT NULL,
`direccion` varchar(255) DEFAULT NULL,
`fecha_pedido` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id_pedido`),
FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
detalle_pedido (Order Items)
Individual products within orders.
CREATE TABLE `detalle_pedido` (
`id_detalle` int NOT NULL AUTO_INCREMENT,
`id_pedido` int NOT NULL,
`id_producto` int NOT NULL,
`cantidad` int DEFAULT 1,
`precio` decimal(10,2) NOT NULL,
PRIMARY KEY (`id_detalle`),
FOREIGN KEY (`id_pedido`) REFERENCES `pedidos` (`id_pedido`) ON DELETE CASCADE,
FOREIGN KEY (`id_producto`) REFERENCES `productos` (`id_producto`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Database Configuration
Character Set and Collation
The database uses UTF-8 encoding for international character support:
- Character Set:
utf8mb4
- Collation:
utf8mb4_general_ci (case-insensitive)
Always use utf8mb4 instead of utf8 to support full Unicode characters including emojis and special symbols.
Storage Engine
All tables use the InnoDB storage engine for:
- Transaction support (ACID compliance)
- Foreign key constraints
- Row-level locking
- Crash recovery
Relationships and Constraints
The database enforces referential integrity through foreign key constraints:
Cascade Delete Behavior
- carrito: Deletes when associated user is deleted
- detalle_carrito: Deletes when cart is deleted
- detalle_pedido: Deletes when order is deleted
- productos: Deletes when vendor (user) is deleted
Set Null Behavior
- pedidos: Sets
id_usuario to NULL when user is deleted (preserves order history)
Initial Data
The SQL import includes sample data:
- Categories: Laptops, Smartphones
- Products: iPhone 15 Pro, MacBook Pro M3, iMac, iPad, and more
- Sample User: frank ([email protected])
Delete or modify the sample data before deploying to production.
Connection Parameters
Default connection settings used by the platform:
| Parameter | Value |
|---|
| Host | 127.0.0.1 or localhost |
| Port | 522 (PHP) / 3306 (Node.js) |
| Database | tienda_online |
| User | root |
| Password | Empty (development only) |
Never use empty passwords or root user access in production environments.
Troubleshooting
Connection Issues
If you encounter connection errors:
- Verify MySQL is running:
sudo systemctl status mysql
- Check port configuration matches your MySQL setup
- Ensure firewall allows connections on the MySQL port
- Verify user credentials and permissions
Schema Conflicts
If tables already exist:
DROP DATABASE tienda_online;
CREATE DATABASE tienda_online;
Next Steps
After setting up the database:
- Configure environment variables
- Set up the PHP backend
- Set up the Node.js backend