SQL Database Design - Airline Booking System
The Challenge
Airlines needed a robust database to replace flat Excel files that caused data redundancy, inconsistent records, and manual reconciliation nightmares.
What I Built
Designed a normalized (3NF) relational database with 6 core tables, 3 stored procedures, 2 triggers, and optimized indexes handling the complete booking workflow.
System Capabilities:
• 6 normalized tables with enforced referential integrity via foreign keys
• 3 stored procedures automating payment updates and passenger management
• 2 triggers enforcing business rules (date validation, email format)
• 4 optimized indexes improving query performance on frequent lookups
Technical Approach
Payments table with Constraints:
CREATE TABLE payments (
payment_id VARCHAR(50) PRIMARY KEY,
booking_id INT UNSIGNED NOT NULL,
payment_date DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_status ENUM ('Success','Failed') NOT NULL,
payment_method ENUM ('Card','Transfer','Wallet') DEFAULT 'Card',
CONSTRAINT fk_payment_booking
FOREIGN KEY (booking_id)
REFERENCES bookings(booking_id)
ON DELETE CASCADE
);
Indexes created:
CREATE INDEX idx_booking_passenger ON bookings(passenger_id);
CREATE INDEX idx_flight_route ON flights(departure_city, arrival_city);
CREATE INDEX idx_flight_departure ON flights(departure_datetime);
CREATE INDEX idx_payment_status ON payments(payment_status);
CREATE TABLE payments (
payment_id VARCHAR(50) PRIMARY KEY,
booking_id INT UNSIGNED NOT NULL,
payment_date DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_status ENUM ('Success','Failed') NOT NULL,
payment_method ENUM ('Card','Transfer','Wallet') DEFAULT 'Card',
CONSTRAINT fk_payment_booking
FOREIGN KEY (booking_id)
REFERENCES bookings(booking_id)
ON DELETE CASCADE
);
Indexes created:
CREATE INDEX idx_booking_passenger ON bookings(passenger_id);
CREATE INDEX idx_flight_route ON flights(departure_city, arrival_city);
CREATE INDEX idx_flight_departure ON flights(departure_datetime);
CREATE INDEX idx_payment_status ON payments(payment_status);
- Overview Normalized airline booking database with 5 core tables, 3 stored procedures, 2 triggers, and optimized indexes. Handles complete booking lifecycle from reservation through payment to ticket issuance with enforced data integrity.
- Technologies Used SQL (MySQL), Database Normalization (3NF), Stored Procedures, Triggers, Indexing, ER Modeling
- Skills Demonstrated Database schema design, SQL DDL and DML, normalization theory (1NF/2NF/3NF), stored procedure development, trigger implementation, index optimization, constraint design, referential integrity, ER modeling
- Business Impact Eliminated data redundancy from flat file system, zero orphaned records through foreign key enforcement, automated common operations via stored procedures reducing manual SQL by 60%, enforced business rules at database level preventing invalid data entry