Database Overview
Database Name
canaccom_travel_db
Engine
MySQL 8.0 / MariaDB 10.6
Total Tables
12 Core Tables
Relationships
Foreign Key Constraints
Table Categories
User Management
3 tables
Hotel Management
4 tables
Booking System
3 tables
System Tables
2 tables
Key Features
ACID Compliance
Referential Integrity
Indexed Performance
Audit Trail
Live Database Tables
View actual database tables and their real-time data
users
User Management| Field | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| id | INT | NO | PRI | AUTO_INCREMENT | Primary key identifier |
| username | VARCHAR(50) | NO | UNI | NULL | Unique username for login |
| VARCHAR(100) | NO | UNI | NULL | User email address | |
| password_hash | VARCHAR(255) | NO | NULL | Hashed password | |
| first_name | VARCHAR(50) | NO | NULL | User's first name | |
| last_name | VARCHAR(50) | NO | NULL | User's last name | |
| role | ENUM('admin','manager','agent','client') | NO | 'client' | User role in system | |
| status | ENUM('active','inactive','suspended') | NO | 'active' | Account status | |
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | Account creation date | |
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP ON UPDATE | Last update timestamp |
hotels
Hotel Management| Field | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| id | INT | NO | PRI | AUTO_INCREMENT | Primary key identifier |
| name | VARCHAR(100) | NO | NULL | Hotel name | |
| description | TEXT | YES | NULL | Hotel description | |
| address | TEXT | NO | NULL | Hotel address | |
| city | VARCHAR(50) | NO | NULL | City location | |
| country | VARCHAR(50) | NO | NULL | Country location | |
| phone | VARCHAR(20) | YES | NULL | Contact phone | |
| VARCHAR(100) | YES | NULL | Contact email | ||
| stars | TINYINT | NO | 3 | Hotel star rating (1-5) | |
| status | ENUM('active','inactive','maintenance') | NO | 'active' | Hotel status | |
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | Creation timestamp | |
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP ON UPDATE | Last update timestamp |
rooms
Hotel Management| Field | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| id | INT | NO | PRI | AUTO_INCREMENT | Primary key identifier |
| hotel_id | INT | NO | MUL | NULL | Foreign key to hotels table |
| room_number | VARCHAR(10) | NO | NULL | Room number/identifier | |
| type | ENUM('single','double','suite','deluxe') | NO | 'single' | Room type | |
| capacity | TINYINT | NO | 1 | Number of guests allowed | |
| price_per_night | DECIMAL(10,2) | NO | 0.00 | Price per night | |
| status | ENUM('available','occupied','maintenance','reserved') | NO | 'available' | Room availability status | |
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | Creation timestamp | |
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP ON UPDATE | Last update timestamp |
bookings
Booking System| Field | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| id | INT | NO | PRI | AUTO_INCREMENT | Primary key identifier |
| user_id | INT | NO | MUL | NULL | Foreign key to users table |
| room_id | INT | NO | MUL | NULL | Foreign key to rooms table |
| check_in_date | DATE | NO | NULL | Check-in date | |
| check_out_date | DATE | NO | NULL | Check-out date | |
| total_amount | DECIMAL(10,2) | NO | 0.00 | Total booking amount | |
| status | ENUM('pending','confirmed','cancelled','completed') | NO | 'pending' | Booking status | |
| payment_status | ENUM('pending','paid','refunded') | NO | 'pending' | Payment status | |
| special_requests | TEXT | YES | NULL | Special requests from guest | |
| created_at | TIMESTAMP | NO | CURRENT_TIMESTAMP | Booking creation timestamp | |
| updated_at | TIMESTAMP | NO | CURRENT_TIMESTAMP ON UPDATE | Last update timestamp |
Additional Tables
User Management
- • user_profiles - Extended user information
- • user_sessions - Login session tracking
Hotel Management
- • hotel_amenities - Hotel facilities
- • hotel_images - Hotel photos
Booking System
- • payments - Payment transactions
- • reviews - Guest reviews
System Tables
- • audit_logs - System activity tracking
- • system_settings - Configuration
Database Relationships
One-to-Many Relationships
hotels → rooms (1:N)
users → bookings (1:N)
rooms → bookings (1:N)
Foreign Key Constraints
rooms.hotel_id → hotels.id
(CASCADE DELETE)
bookings.user_id → users.id
(RESTRICT DELETE)
bookings.room_id → rooms.id
(RESTRICT DELETE)
Indexes
Primary Keys:
- • users.id
- • hotels.id
- • rooms.id
- • bookings.id
Unique Indexes:
- • users.username
- • users.email
- • rooms.hotel_id + room_number
Entity Relationship Diagram
Database ERD
Interactive Entity Relationship Diagram showing table relationships and constraints.
Primary Key
Foreign Key
Unique Index
ERD visualization would be implemented with a diagram library like Mermaid.js or similar