Database Schema

Complete reference for the hotel_guevarini_publico MySQL database, including all tables, columns, constraints, and relationships.

The application uses a single MySQL database named hotel_guevarini_publico. The schema is defined in base_de_datos.sql at the project root and includes 5 tables with seed data for testing.

Entity Relationships

roles ──< usuarios ──< clientes ──< reservaciones
                                         │
                    habitaciones ────────┘

• A role (roles) is assigned to one or many users (usuarios).
• A user (usuarios) may optionally be linked to one client record (clientes). Deleting a user sets the client’s id_usuario to NULL.
• A client (clientes) can have many reservations (reservaciones). Deleting a client cascades and removes their reservations.
• A room (habitaciones) can appear in many reservations (reservaciones). Deleting a room cascades and removes its reservations.

---

Tables

roles — User roles

Stores the two role types available in the system.

Column	Type	Constraints
id_rol	INT	AUTO_INCREMENT, PRIMARY KEY
nombre_rol	VARCHAR(50)	NOT NULL

Seed data

id_rol	nombre_rol
1	Administrador
2	Cliente

CREATE TABLE roles (
    id_rol INT AUTO_INCREMENT PRIMARY KEY,
    nombre_rol VARCHAR(50) NOT NULL
);

INSERT INTO roles (nombre_rol) VALUES
('Administrador'),
('Cliente');

usuarios — System users

Stores login credentials and account verification state for every user.

Column	Type	Constraints
id_usuario	INT	AUTO_INCREMENT, PRIMARY KEY
nombre_completo	VARCHAR(100)	NOT NULL
correo	VARCHAR(100)	NOT NULL, UNIQUE
password	VARCHAR(255)	NOT NULL — bcrypt hash
id_rol	INT	NOT NULL, FK → roles(id_rol)
verificado	TINYINT(1)	DEFAULT 0
token_verificacion	VARCHAR(255)	nullable
token_recuperacion	VARCHAR(255)	nullable
expiracion_token_recuperacion	DATETIME	nullable

Passwords are stored and compared as plain text in this system — no hashing is applied. The VARCHAR(255) column is sized for a future bcrypt implementation but is not currently used. Do not use real or sensitive passwords. The system is intended for local development and learning purposes only.

CREATE TABLE usuarios (
    id_usuario INT AUTO_INCREMENT PRIMARY KEY,
    nombre_completo VARCHAR(100) NOT NULL,
    correo VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    id_rol INT NOT NULL,
    verificado TINYINT(1) DEFAULT 0,
    token_verificacion VARCHAR(255) DEFAULT NULL,
    token_recuperacion VARCHAR(255) DEFAULT NULL,
    expiracion_token_recuperacion DATETIME DEFAULT NULL,
    FOREIGN KEY (id_rol) REFERENCES roles(id_rol)
);

clientes — Hotel guests

Stores guest contact information. A client record can exist independently of a user account.

Column	Type	Constraints
id_cliente	INT	AUTO_INCREMENT, PRIMARY KEY
id_usuario	INT	nullable, FK → usuarios(id_usuario) ON DELETE SET NULL
nombre_completo	VARCHAR(100)	NOT NULL
telefono	VARCHAR(20)	NOT NULL
estado	ENUM('Activo', 'Inactivo')	DEFAULT 'Activo'

Seed data

nombre_completo	telefono	estado
Carlos López	555-3841	Activo
Ana Martínez	555-7734	Inactivo

CREATE TABLE clientes (
    id_cliente INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT DEFAULT NULL,
    nombre_completo VARCHAR(100) NOT NULL,
    telefono VARCHAR(20) NOT NULL,
    estado ENUM('Activo', 'Inactivo') DEFAULT 'Activo',
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario) ON DELETE SET NULL
);

habitaciones — Rooms

Tracks every room in the hotel, its category, nightly price, and availability status.

Column	Type	Constraints
id_habitacion	INT	AUTO_INCREMENT, PRIMARY KEY
numero	VARCHAR(10)	NOT NULL, UNIQUE
tipo	VARCHAR(50)	NOT NULL
precio	DECIMAL(10,2)	NOT NULL
estado	ENUM('Disponible', 'Ocupada', 'Mantenimiento')	DEFAULT 'Disponible'

Seed data

numero	tipo	precio	estado
101A	Sencilla	50.00	Disponible
105B	Doble	85.00	Ocupada
201C	Suite	150.00	Mantenimiento
302A	Doble	85.00	Disponible

CREATE TABLE habitaciones (
    id_habitacion INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(10) NOT NULL UNIQUE,
    tipo VARCHAR(50) NOT NULL,
    precio DECIMAL(10,2) NOT NULL,
    estado ENUM('Disponible', 'Ocupada', 'Mantenimiento') DEFAULT 'Disponible'
);

reservaciones — Bookings

Records each reservation linking a client to a room for a specific date range.

Column	Type	Constraints
id_reserva	INT	AUTO_INCREMENT, PRIMARY KEY
codigo	VARCHAR(20)	NOT NULL, UNIQUE
id_cliente	INT	NOT NULL, FK → clientes(id_cliente) ON DELETE CASCADE
id_habitacion	INT	NOT NULL, FK → habitaciones(id_habitacion) ON DELETE CASCADE
fecha_entrada	DATE	NOT NULL
fecha_salida	DATE	NOT NULL
estado	ENUM('Confirmada', 'Pendiente', 'Cancelada')	DEFAULT 'Pendiente'

Seed data

codigo	id_cliente	id_habitacion	fecha_entrada	fecha_salida	estado
RES-9912	1	2	2026-03-20	2026-03-25	Confirmada
RES-9934	2	3	2026-04-10	2026-04-15	Pendiente

CREATE TABLE reservaciones (
    id_reserva INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(20) NOT NULL UNIQUE,
    id_cliente INT NOT NULL,
    id_habitacion INT NOT NULL,
    fecha_entrada DATE NOT NULL,
    fecha_salida DATE NOT NULL,
    estado ENUM('Confirmada', 'Pendiente', 'Cancelada') DEFAULT 'Pendiente',
    FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente) ON DELETE CASCADE,
    FOREIGN KEY (id_habitacion) REFERENCES habitaciones(id_habitacion) ON DELETE CASCADE
);

---

Importing the Schema

Run the following command from a MySQL client to create the database, all tables, and seed records in one step:

mysql -u root -p < base_de_datos.sql

Or import base_de_datos.sql through your GUI client (phpMyAdmin, DBeaver, MySQL Workbench).

The seed user passwords (12345) are stored as plain text, and the system compares passwords as plain text at runtime — no hashing is applied. Only use test passwords in local environments. Never deploy this system to a public URL without adding proper password hashing first.