CloudSketch AI Logo
FeaturesTemplatesPricingEnterpriseAboutContactLog in
🌙☀️
Log inStart free
Home/Templates/Aviation

🛫 Aviation · Database ERD

Airline Frequent Flyer Schema

Tables for an airline frequent flyer programme: members, tiers, flights flown, miles earned and redemptions.

More Aviation templates

Opening a template is free and never counts toward your daily diagrams. Asking the AI to change it counts as one.

Drawing diagram…

What this diagram shows

  • Miles are earned per flight segment and expire
  • Tier status depends on qualifying miles in a year
  • Redemptions reduce the member's balance

Prompt used

Frequent flyer programme database: members belong to a tier; flight segments flown by a member earn miles (with fare class and expiry date); partner transactions also earn miles; redemptions (award tickets, upgrades) spend miles; tier reviews record status changes.

Mermaid code
erDiagram
  TIER ||--o{ MEMBER : has
  MEMBER ||--o{ FLIGHT_SEGMENT : flies
  FLIGHT_SEGMENT ||--|| MILES_TXN : earns
  MEMBER ||--o{ MILES_TXN : "balance of"
  PARTNER ||--o{ MILES_TXN : "partner earn"
  MEMBER ||--o{ REDEMPTION : makes
  MEMBER ||--o{ TIER_REVIEW : "reviewed in"
  TIER {
    int id PK
    string name
    int qualifying_miles
  }
  MEMBER {
    int id PK
    int tier_id FK
    string ff_number
    string name
  }
  FLIGHT_SEGMENT {
    int id PK
    int member_id FK
    string flight_no
    date flown_on
    string fare_class
  }
  MILES_TXN {
    int id PK
    int member_id FK
    int partner_id FK
    int miles
    date expires_on
  }
  PARTNER {
    int id PK
    string name
    string category
  }
  REDEMPTION {
    int id PK
    int member_id FK
    string type
    int miles_used
  }
  TIER_REVIEW {
    int id PK
    int member_id FK
    date reviewed_on
    string result
  }

Inside this diagram

An entity relationship diagram with 7 tables, 28 columns and 7 relationships.

Tables and their columns

  • TIER: id (PK), name, qualifying_miles
  • MEMBER: id (PK), tier_id (FK), ff_number, name
  • FLIGHT_SEGMENT: id (PK), member_id (FK), flight_no, flown_on, fare_class
  • MILES_TXN: id (PK), member_id (FK), partner_id (FK), miles, expires_on
  • PARTNER: id (PK), name, category
  • REDEMPTION: id (PK), member_id (FK), type, miles_used
  • TIER_REVIEW: id (PK), member_id (FK), reviewed_on, result

Relationships

  • TIER has MEMBER: each TIER has zero or more MEMBER, each MEMBER belongs to exactly one TIER
  • MEMBER flies FLIGHT_SEGMENT: each MEMBER has zero or more FLIGHT_SEGMENT, each FLIGHT_SEGMENT belongs to exactly one MEMBER
  • FLIGHT_SEGMENT earns MILES_TXN: each FLIGHT_SEGMENT has exactly one MILES_TXN, each MILES_TXN belongs to exactly one FLIGHT_SEGMENT
  • MEMBER balance of MILES_TXN: each MEMBER has zero or more MILES_TXN, each MILES_TXN belongs to exactly one MEMBER
  • PARTNER partner earn MILES_TXN: each PARTNER has zero or more MILES_TXN, each MILES_TXN belongs to exactly one PARTNER
  • MEMBER makes REDEMPTION: each MEMBER has zero or more REDEMPTION, each REDEMPTION belongs to exactly one MEMBER
  • MEMBER reviewed in TIER_REVIEW: each MEMBER has zero or more TIER_REVIEW, each TIER_REVIEW belongs to exactly one MEMBER

Make your own diagram like this

Describe your own system in plain English and the AI ER diagram generator draws it in seconds, in the same style as this template.

Related templates

FlowchartAviation

Airport Baggage Handling Flow

The journey of a checked bag through an airport: check-in, security screening, sorting to the right flight and loading, with exceptions.

State MachineAviation

Aircraft Maintenance Work Order Lifecycle

How a maintenance work order moves in an airline MRO: from a pilot's defect report to inspection, parts, repair and certificate of release to service.

C4 ArchitectureProAviation

Airport Operations Platform

The systems that run an airport day to day: operational database, resource management for gates and stands, flight information displays and collaborative decision-making.

Data FlowAviation

Flight Operations Data Flow

How flight data moves around an airline's operations: flight planning, the aircraft's ACARS messages, flight data monitoring and crew reports.

GanttAviation

Airline Crew Scheduling Timeline

A monthly crew planning cycle for an airline: pairing construction, bidding, roster publishing and day-of-operations changes.

NetworkAviation

Airport IT Network Topology

The IT network of an airport: separate zones for passenger Wi-Fi, common-use terminal equipment, operations systems and airline links.