✈️ Travel & Hospitality · Database ERD

Travel Loyalty Programme Schema

A database schema for an airline or hotel loyalty programme: members, tiers, earning and burning points, partners and rewards.

More Travel & Hospitality templates

Drawing diagram…

What this diagram shows

Every points movement is a transaction with an expiry
Tier depends on qualifying activity in a period
Partners (cards, car rental) can award points

Prompt used

ERD for a loyalty programme: Member belongs to a Tier (Silver, Gold, Platinum); PointsTransaction records earn and burn with expiry dates and source (Stay/Flight, Partner); Partner can award points; RewardCatalog items are redeemed through Redemptions; TierQualification tracks qualifying nights or flights per year.

Mermaid code

erDiagram
  TIER ||--o{ MEMBER : "current tier of"
  MEMBER ||--o{ POINTS_TRANSACTION : has
  PARTNER ||--o{ POINTS_TRANSACTION : awards
  MEMBER ||--o{ REDEMPTION : makes
  REWARD ||--o{ REDEMPTION : "redeemed as"
  MEMBER ||--o{ TIER_QUALIFICATION : tracks
  MEMBER {
    int id PK
    string member_no
    int tier_id FK
    int points_balance
  }
  POINTS_TRANSACTION {
    int id PK
    int member_id FK
    string type
    int points
    date expires_on
  }
  REWARD {
    int id PK
    string name
    int points_cost
  }
  TIER_QUALIFICATION {
    int member_id FK
    int year
    int qualifying_nights
  }

Related templates

SequenceTravel & Hospitality

Flight Booking Sequence

How an online travel site books a flight: fare search across airlines, seat hold, passenger details, payment and ticket issue.

State MachineTravel & Hospitality

Hotel Reservation Lifecycle

Every state of a hotel reservation from enquiry to checkout, including pay-at-hotel, cancellations and no-shows.

C4 ArchitectureProTravel & Hospitality

Hotel Reservation System Architecture

A hotel chain's reservation platform: booking engine, property management system, channel manager for OTAs and revenue management.

FlowchartTravel & Hospitality

Airline Check-In Flow

How a passenger checks in for a flight: online or at the airport, seat selection, documents check, bags and boarding pass.

DeploymentProTravel & Hospitality

Travel Aggregator Deployment

How an online travel aggregator is deployed to handle search spikes: search fan-out to suppliers, caching, and separate booking services.

SequenceTravel & Hospitality

Hotel Digital Check-In Sequence

Mobile check-in for a hotel: ID upload, room assignment by the PMS, payment hold and a digital room key.