SQL 航空公司票务系统项目实战：航班预订与票务管理

xiaoshi 05-30 8 抢沙发

默认

摘要： ...

SQL航空公司票务系统实战：构建高效的航班预订与管理平台

项目背景与系统概述

现代航空业面临着日益增长的旅客需求和复杂的票务管理挑战。一个基于SQL数据库的航空公司票务系统能够有效解决这些问题，实现航班信息管理、座位预订、票价计算和客户服务等核心功能。本文将详细介绍如何从零开始构建这样一个系统，涵盖数据库设计、功能实现和性能优化等关键环节。

数据库设计与建模

核心表结构设计是票务系统的基石。我们需要创建航班表(flights)存储航班号、起降时间、航线等基本信息；飞机表(aircrafts)记录机型、座位数等数据；乘客表(passengers)管理旅客个人信息；预订表(bookings)则关联乘客与航班，记录座位选择和票务状态。

CREATE TABLE flights (
    flight_id INT PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    departure_airport VARCHAR(3) NOT NULL,
    arrival_airport VARCHAR(3) NOT NULL,
    departure_time DATETIME NOT NULL,
    arrival_time DATETIME NOT NULL,
    aircraft_id INT NOT NULL,
    base_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (aircraft_id) REFERENCES aircrafts(aircraft_id)
);

CREATE TABLE bookings (
    booking_id INT PRIMARY KEY,
    flight_id INT NOT NULL,
    passenger_id INT NOT NULL,
    seat_number VARCHAR(5) NOT NULL,
    booking_time DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (flight_id) REFERENCES flights(flight_id),
    FOREIGN KEY (passenger_id) REFERENCES passengers(passenger_id)
);

系统功能实现

实时航班查询功能

高效的航班查询功能是票务系统的核心。我们可以通过优化SQL查询来快速检索符合条件的航班：

SELECT f.flight_number, f.departure_airport, f.arrival_airport, 
       f.departure_time, f.arrival_time, a.model AS aircraft_model,
       COUNT(b.booking_id) AS booked_seats,
       (a.total_seats - COUNT(b.booking_id)) AS available_seats,
       f.base_price * (1 + d.demand_multiplier) AS current_price
FROM flights f
JOIN aircrafts a ON f.aircraft_id = a.aircraft_id
LEFT JOIN bookings b ON f.flight_id = b.flight_id AND b.status = 'confirmed'
JOIN demand_factors d ON f.flight_id = d.flight_id
WHERE f.departure_airport = 'PEK' 
  AND f.arrival_airport = 'SHA'
  AND DATE(f.departure_time) = '2023-11-15'
GROUP BY f.flight_id
ORDER BY f.departure_time;

动态定价策略

现代航空票务系统普遍采用动态定价算法，考虑预订时间、剩余座位数和市场需求等因素：

-- 动态价格计算存储过程
CREATE PROCEDURE calculate_dynamic_price(IN p_flight_id INT, OUT p_final_price DECIMAL(10,2))
BEGIN
    DECLARE v_base_price DECIMAL(10,2);
    DECLARE v_booked_seats INT;
    DECLARE v_total_seats INT;
    DECLARE v_occupancy_rate DECIMAL(5,2);
    DECLARE v_days_until_departure INT;
    DECLARE v_demand_factor DECIMAL(5,2);

    -- 获取基础数据
    SELECT f.base_price, COUNT(b.booking_id), a.total_seats,
           DATEDIFF(f.departure_time, NOW())
    INTO v_base_price, v_booked_seats, v_total_seats, v_days_until_departure
    FROM flights f
    JOIN aircrafts a ON f.aircraft_id = a.aircraft_id
    LEFT JOIN bookings b ON f.flight_id = b.flight_id AND b.status = 'confirmed'
    WHERE f.flight_id = p_flight_id;

    -- 计算占用率
    SET v_occupancy_rate = (v_booked_seats / v_total_seats) * 100;

    -- 根据提前预订天数调整
    IF v_days_until_departure > 30 THEN
        SET v_demand_factor = 0.9; -- 提前预订折扣
    ELSEIF v_days_until_departure > 14 THEN
        SET v_demand_factor = 1.0; -- 标准价格
    ELSEIF v_days_until_departure > 7 THEN
        SET v_demand_factor = 1.2; -- 临近出发溢价
    ELSE
        SET v_demand_factor = 1.5; -- 最后时刻高价
    END IF;

    -- 根据座位占用率调整
    IF v_occupancy_rate > 80 THEN
        SET v_demand_factor = v_demand_factor * 1.3;
    ELSEIF v_occupancy_rate > 60 THEN
        SET v_demand_factor = v_demand_factor * 1.1;
    END IF;

    -- 计算最终价格
    SET p_final_price = v_base_price * v_demand_factor;
END;

高级功能实现

座位选择与分配

智能座位分配功能可以提升用户体验和航空公司运营效率：

-- 查找可用座位
SELECT s.seat_number, s.seat_class, s.extra_legroom, s.window_seat
FROM seats s
WHERE s.aircraft_id = (SELECT aircraft_id FROM flights WHERE flight_id = 123)
AND s.seat_number NOT IN (
    SELECT seat_number FROM bookings 
    WHERE flight_id = 123 AND status IN ('confirmed', 'pending')
)
ORDER BY 
    CASE WHEN s.seat_class = 'business' THEN 1
         WHEN s.seat_class = 'premium' THEN 2
         ELSE 3 END,
    s.extra_legroom DESC,
    s.window_seat DESC;

机票改签与退票

票务变更处理是客户服务的重要环节，需要严谨的事务管理：

-- 机票改签事务处理
START TRANSACTION;

-- 1. 释放原航班座位
UPDATE bookings 
SET status = 'cancelled', 
    cancellation_time = NOW(),
    refund_amount = total_price * 0.8
WHERE booking_id = 456 AND status = 'confirmed';

-- 2. 检查新航班可用性
SELECT COUNT(*) INTO @available_seats
FROM seats s
WHERE s.aircraft_id = (SELECT aircraft_id FROM flights WHERE flight_id = 789)
AND s.seat_number NOT IN (
    SELECT seat_number FROM bookings 
    WHERE flight_id = 789 AND status IN ('confirmed', 'pending')
);

IF @available_seats > 0 THEN
    -- 3. 在新航班上创建预订
    INSERT INTO bookings (flight_id, passenger_id, seat_number, booking_time, status, total_price)
    SELECT 789, passenger_id, 
           (SELECT seat_number FROM bookings WHERE booking_id = 456),
           NOW(), 'confirmed',
           (SELECT base_price FROM flights WHERE flight_id = 789) * 1.1 -- 改签费10%
    FROM bookings
    WHERE booking_id = 456;

    COMMIT;
    SELECT '改签成功' AS result;
ELSE
    ROLLBACK;
    SELECT '新航班已满，改签失败' AS result;
END IF;

系统优化与安全

查询性能优化

大型航空公司的票务系统每天处理数百万次查询，数据库优化至关重要：

-- 为常用查询创建索引
CREATE INDEX idx_flights_route ON flights(departure_airport, arrival_airport, departure_time);
CREATE INDEX idx_bookings_flight ON bookings(flight_id, status);
CREATE INDEX idx_bookings_passenger ON bookings(passenger_id);

-- 使用物化视图预计算热门航线数据
CREATE MATERIALIZED VIEW popular_routes AS
SELECT departure_airport, arrival_airport, COUNT(*) AS flight_count,
       AVG(base_price) AS avg_price, MIN(departure_time) AS next_departure
FROM flights
WHERE departure_time > NOW()
GROUP BY departure_airport, arrival_airport
ORDER BY flight_count DESC
LIMIT 100;

-- 定期刷新物化视图
REFRESH MATERIALIZED VIEW popular_routes;

数据安全与备份

票务数据保护需要多层次的策略：

-- 实施行级安全策略
CREATE POLICY passenger_data_policy ON passengers
    USING (passenger_id IN (
        SELECT passenger_id FROM bookings 
        WHERE booking_id = current_setting('app.current_booking_id')::INT
    ));

-- 定期备份关键表
CREATE EVENT backup_critical_tables
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    -- 导出预订数据
    SELECT * FROM bookings 
    WHERE booking_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
    INTO OUTFILE '/backups/recent_bookings.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

    -- 导出航班变动
    SELECT * FROM flight_changes
    WHERE change_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
    INTO OUTFILE '/backups/recent_changes.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
END;