Airline Frequent Flyer Schema

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
  }