This project implements a complete database system for an AirBnb clone application. It includes a normalized database schema with comprehensive sample data, designed to support all core features of a vacation rental platform.
alx-airbnb-database/
├── README.md # This file - Project overview
├── database-script-0x01/ # Database schema implementation
│ ├── README.md # Schema documentation
│ └── schema.sql # Complete database schema
├── database-script-0x02/ # Sample data implementation
│ ├── README.md # Seed data documentation
│ └── seed.sql # Comprehensive sample data
├── ERD/ # Entity Relationship Diagrams
│ ├── airbnb-clone-database-updated.png
│ └── requirements.md
└── normalization.md # Normalization documentation
- Diverse Property Types: Apartments, houses, cabins, villas, lofts
- Location-Based Search: Multiple location categories and districts
- Pricing System: Flexible pricing with historical tracking
- Host Management: Multiple properties per host
- Role-Based Access: Guests, hosts, and administrators
- User Profiles: Complete user information and preferences
- Authentication: Secure password storage and user management
- Reservation Management: Complete booking lifecycle
- Status Tracking: Pending, confirmed, and canceled bookings
- Date Validation: Proper start/end date constraints
- Price Locking: Historical price tracking for bookings
- Multiple Payment Methods: Credit card, PayPal, Stripe
- Payment Tracking: Complete payment history and records
- Amount Calculation: Accurate payment amounts based on duration
- Rating System: 1-5 star ratings with detailed comments
- Property Reviews: Guest feedback on properties and experiences
- Host Feedback: Reviews for host responsiveness and service
- User Messaging: Direct communication between users
- Host-Guest Communication: Booking inquiries and support
- Community Building: Guest-to-guest and host-to-host interactions
- 1NF (First Normal Form): Atomic values, no repeating groups
- 2NF (Second Normal Form): No partial dependencies
- 3NF (Third Normal Form): No transitive dependencies
- Strategic Indexing: Optimized for common query patterns
- Composite Indexes: Multi-column indexes for complex queries
- Query Optimization: Efficient data retrieval and joins
- Foreign Key Constraints: Maintained referential integrity
- Check Constraints: Data validation and business rules
- Unique Constraints: Prevent duplicate data entry
- 6 Core Tables: users, properties, bookings, payments, reviews, messages
- Comprehensive Constraints: Data validation and business rules
- Performance Indexes: Optimized for common operations
- Audit Trail: Timestamp tracking for all entities
- 14 Users: 5 hosts, 8 guests, 1 admin
- 10 Properties: Diverse accommodation types and locations
- 14 Bookings: Various statuses and scenarios
- 8 Payments: Multiple payment methods and amounts
- 11 Reviews: Authentic feedback and ratings
- 25 Messages: Realistic user interactions
# Create the database schema
mysql -u username -p < database-script-0x01/schema.sql
# Populate with sample data
mysql -u username -p airbnb_clone < database-script-0x02/seed.sql-- Check database structure
USE airbnb_clone;
SHOW TABLES;
-- Verify sample data
SELECT COUNT(*) as total_users FROM users;
SELECT COUNT(*) as total_properties FROM properties;
SELECT COUNT(*) as total_bookings FROM bookings;-- Find all properties by a specific host
SELECT p.*, u.first_name, u.last_name
FROM properties p
JOIN users u ON p.host_id = u.id
WHERE u.id = 1;
-- Get booking history for a user
SELECT b.*, p.name as property_name, p.location
FROM bookings b
JOIN properties p ON b.property_id = p.id
WHERE b.user_id = 6
ORDER BY b.created_at DESC;
-- Calculate average rating for properties
SELECT p.name, AVG(r.rating) as avg_rating, COUNT(r.id) as review_count
FROM properties p
LEFT JOIN reviews r ON p.id = r.property_id
GROUP BY p.id, p.name
ORDER BY avg_rating DESC;- Application Development: Complete database foundation for AirBnb clone
- Testing: Comprehensive test data for all features
- API Development: Ready-to-use data for backend development
- Performance Testing: Optimized schema for load testing
- Schema Design: Best practices for vacation rental platforms
- Performance Tuning: Indexing strategies and optimization
- Data Modeling: Normalized design patterns
- Maintenance: Audit trails and data integrity
- Data Analysis: Rich dataset for business intelligence
- User Behavior: Booking patterns and preferences
- Revenue Analysis: Payment tracking and pricing analysis
- Customer Insights: Review analysis and feedback patterns
- Database: MySQL 8.0+
- Character Set: UTF-8
- Collation: utf8mb4_unicode_ci
- Storage Engine: InnoDB
- Auto-incrementing IDs: All primary keys
- Timestamp Tracking: Created/updated timestamps
- Enum Constraints: Status and type fields
- Decimal Precision: Accurate financial calculations
- Users: 14 sample users
- Properties: 10 diverse properties
- Bookings: 14 realistic bookings
- Reviews: 11 authentic reviews
- Messages: 25 conversation threads
- Password Hashing: Secure password storage
- Input Validation: Check constraints and data types
- Access Control: Role-based permissions
- Audit Logging: Timestamp tracking for changes
- Personal Data: Fictional sample data only
- Data Minimization: Only necessary fields included
- Consent Management: User role-based data access
- Data Retention: Timestamp-based audit trails
- Modular Design: Independent table structures
- Index Optimization: Efficient query performance
- Partitioning Ready: Date-based partitioning support
- Sharding Compatible: User-based sharding possible
- Efficient Indexes: Optimized for common queries
- Memory Optimization: Balanced data types
- Query Optimization: Strategic indexing strategy
- Connection Pooling: Efficient resource utilization
- Index Optimization: Monitor and update indexes
- Data Archiving: Historical data management
- Performance Monitoring: Query performance tracking
- Backup Strategies: Regular database backups
- Schema Evolution: Version-controlled schema changes
- Data Migration: Safe data structure updates
- Feature Additions: Extensible design patterns
- Documentation: Comprehensive documentation updates
- Schema Changes: Update both schema and seed files
- Documentation: Maintain comprehensive README files
- Testing: Verify data integrity and relationships
- Performance: Consider query optimization impact
- SQL Formatting: Consistent SQL style and formatting
- Naming Conventions: Clear and descriptive names
- Comments: Comprehensive code documentation
- Version Control: Proper commit messages and history
This project is designed for educational and development purposes. The database schema and sample data can be used for:
- Learning database design principles
- Developing AirBnb clone applications
- Testing and demonstration purposes
- Academic and research projects
- Schema Documentation:
database-script-0x01/README.md - Seed Data Documentation:
database-script-0x02/README.md - ERD Diagrams:
ERD/directory - Normalization Guide:
normalization.md
- MySQL Documentation: Official MySQL reference
- Database Design: Best practices and patterns
- Performance Tuning: Query optimization guides
- Security Guidelines: Database security best practices