The airline model, rebuilt small

Old model

Couldn't answer

fares.price only held today's price. A new price overwrote the old one, so there was nothing to compare. Nothing recorded who wanted alerts.

What was added

• FareHistory: a new row every time a price changes. Old price, new price, when.
• PriceAlert: links a Client to a Flight they want alerts for.

Why a table in the middle? One client can watch many flights. One flight can be watched by many clients. That is many-to-many, and many-to-many always becomes a link table.

The SQL

SELECT  c.name, c.email, f.flight_number,
        fa.passenger_type, fh.old_price, fh.new_price
FROM    FareHistory fh
JOIN    Fare       fa ON fa.fare_id   = fh.fare_id
JOIN    Flight     f  ON f.flight_id  = fa.flight_id
JOIN    PriceAlert pa ON pa.flight_id = f.flight_id
JOIN    Client     c  ON c.client_id  = pa.client_id
WHERE   fh.changed_at >= '2019-11-01'   -- since the last run
ORDER BY c.name;

Read it like this

1. Start at FareHistory: every price change.
2. Join Fare, then Flight: which flight the price belongs to.
3. Join PriceAlert: who is watching that flight.
4. Join Client: their name and email.
5. Keep only changes since the program last ran.

Old model

Half

It could find the airline, and the departure airport's country. It had no customer country.

Decide first: "which country" could mean the customer's country or the departure airport's country. This example uses the customer's, because that's where demand comes from.

What was added

• Country: a lookup table. Each country is written once.
• Client.country_id: points at Country.
• Airport.country_id: points at the same table, so "UK" is never spelt two ways.

Not a normalisation problem. The old tables were linked fine. The query would not fail; the country just wasn't stored anywhere.

The SQL

SELECT  co.name AS country, COUNT(*) AS bookings
FROM    Booking b
JOIN    Flight  f  ON f.flight_id   = b.flight_id
JOIN    Airline a  ON a.airline_id  = f.airline_id
JOIN    Client  c  ON c.client_id   = b.client_id
JOIN    Country co ON co.country_id = c.country_id
WHERE   a.name = 'Emirates'
  AND   b.status = 'confirmed'
GROUP BY co.country_id, co.name
ORDER BY bookings DESC;

Read it like this

1. Start at Booking.
2. Join Flight and Airline. Keep only Emirates.
3. Join Client and Country to get each booking's country.
4. Leave out cancelled bookings.
5. GROUP BY country, then COUNT the rows in each group.
6. ORDER BY the count, biggest first (DESC).

Old model

Couldn't answer

Fares had no child price. There was no meal data. dietary_restrictions is what a passenger needs, not what a flight offers.

Watch out: filter on the flight's departure date. The booking date is when someone paid, not when they fly.

What was added

• Fare.passenger_type: adult, child or infant. A child discount is a child fare row.
• Flight.offers_kids_meal: yes or no.
• Passenger.date_of_birth: so a child fare can be checked against a real child.

Simple vs flexible. Yes/no is the simple option. If the airline had many meal types, a Meal table plus a link table would be better.

The SQL

SELECT DISTINCT f.flight_number, f.departure_time
FROM    Flight f
JOIN    Fare fa ON fa.flight_id = f.flight_id
WHERE   f.departure_time >= '2019-12-01'
  AND   f.departure_time <  '2020-01-01'
  AND   f.offers_kids_meal = TRUE
  AND   fa.passenger_type  = 'child'
ORDER BY f.departure_time;

Read it like this

1. Start at Flight.
2. Keep flights that depart in December 2019.
3. Keep flights that offer a kids' meal.
4. Join Fare. Keep flights that have a child fare.
5. DISTINCT: show each flight once, even if it has two child fares.

Stretch: check the child price is lower than the adult price. That needs a subquery (textbook 6.3.5).