Back

Airline Database

Live

2025 Database Design

A comprehensive PostgreSQL database schema that models the full lifecycle of commercial flight operations from global airport networks and flight scheduling to crew management, aircraft certification, and the complete passenger booking journey. Designed as a production-ready backend foundation for airlines, airports, and travel platforms, with business rules enforced directly at the database layer.

Problem

Airline operations involve dozens of tightly interdependent entities: flights, routes, crews, aircraft, gates, passengers, bookings, and international travel requirements, each governed by strict operational and regulatory rules. Modelling this in a way that prevents invalid states (e.g., a crew member exceeding legal shift limits, a passenger booked internationally without the required visa, or orphaned records after a deletion) is a significant data-integrity challenge. This project solves that by designing a fully normalized relational schema where business logic and constraints live in the database itself, rather than relying on application code to catch violations.

Highlights

  1. Designed a normalized 20+ table relational schema spanning five operational domains (airport network, scheduling, aircraft, crew, and passenger/booking lifecycle), demonstrating end-to-end data modelling of a complex real-world domain.
  2. Enforced business rules at the database layer using foreign-key constraints, CHECK clauses, and cascade policies for example, a 14-hour maximum crew shift limit, visa requirements for international routes, and controlled ON DELETE behaviour to maintain referential integrity.
  3. Created analytical SQL queries involving aggregation, window functions, and multi-table joins such as ranking top travellers per route, computing passenger throughput per airport, and identifying frequent crew–aircraft pairings across a year of operations.
  4. Applied schema design best practices: high normalization to eliminate redundancy, reusable scheduling templates (FlightTimes) for modularity, and an extensible structure that accommodates future entities like cargo or loyalty tiers without redesign.
  5. Delivered a deployable, documented project with schema, seed data, and runnable query scripts, including clear setup instructions showing attention to reproducibility and developer experience.

Tech & skills

  • Back-End Web Development
  • Custom Software
  • Database Design
  • Git
  • GitHub
  • PostgreSQL
  • Requirements Gathering
  • SQL

Links