> But if your natural key is the thing that changed you’d never know that airport x was renamed to airport y.
Correct, which is why you need the addition of a DATETIME to indicate when that identifier is valid.
> And when you renamed the airport, you’d need to add new entries in all the other tables that used airport name as a foreign key
No, because you wouldn't use the name in the key, you'd use a code like ICAO, though there are pseudo-ICAO codes for some aerodromes, so whether or not you want to be pedantic about naming is a personal choice. Then use FK constraints. Example:
CREATE TABLE airport_physical (
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
coordinates POINT NOT NULL,
country_code_alpha2 CHARACTER(2) NOT NULL, -- ideally this would be a FK to an ISO3166-2 table
opened_at DATE NOT NULL,
closed_at DATE DEFAULT 'infinity'
);
CREATE TABLE airport_code (
icao TEXT NOT NULL CHECK (length(icao) <= 16), -- reasonable length, but can easily be changed
iata CHAR(3) DEFAULT NULL,
airport_physical_id INTEGER NOT NULL REFERENCES airport_physical(id) ON UPDATE CASCADE ON DELETE RESTRICT,
effective_date DATE NOT NULL,
end_date DATE NOT NULL DEFAULT 'infinity',
PRIMARY KEY (icao, effective_date)
);
CREATE TABLE airport_name (
airport_physical_id INTEGER NOT NULL REFERENCES airport_physical(id) ON UPDATE CASCADE ON DELETE RESTRICT,
name TEXT NOT NULL CHECK (length(name) <= 126),
effective_date DATE NOT NULL,
end_date DATE DEFAULT 'infinity',
PRIMARY KEY (airport_physical_id, effective_date, name)
);
This would let you model edge cases like John F. Kennedy International Airport, née Idlewild Airport, which had the ICAO "KIDL" from its opening (I mean, probably before that as well, but for the sake of argument assume you care when it was operating) on 1948-07-01 until 1964-01-01, but its name was changed to John F. Kennedy on 1963-12-24. It also allows you to model the reuse of ICAO codes, since Indianoloa Municipal Airport received the ICAO code "KIDL" following its release by JFK.
Is this easier to do than surrogate keys? Not really, no, but IMO it's easier for a human to understand when presented with temporal changes, allows for edge cases like an airport's designator or name changing while flights are enroute, and for flights (which would be the largest table), they can use `icao` and their departure/arrival datetime (which the table would need to model anyway) to effectively link to the other tables.
Correct, which is why you need the addition of a DATETIME to indicate when that identifier is valid.
> And when you renamed the airport, you’d need to add new entries in all the other tables that used airport name as a foreign key
No, because you wouldn't use the name in the key, you'd use a code like ICAO, though there are pseudo-ICAO codes for some aerodromes, so whether or not you want to be pedantic about naming is a personal choice. Then use FK constraints. Example:
This would let you model edge cases like John F. Kennedy International Airport, née Idlewild Airport, which had the ICAO "KIDL" from its opening (I mean, probably before that as well, but for the sake of argument assume you care when it was operating) on 1948-07-01 until 1964-01-01, but its name was changed to John F. Kennedy on 1963-12-24. It also allows you to model the reuse of ICAO codes, since Indianoloa Municipal Airport received the ICAO code "KIDL" following its release by JFK.Is this easier to do than surrogate keys? Not really, no, but IMO it's easier for a human to understand when presented with temporal changes, allows for edge cases like an airport's designator or name changing while flights are enroute, and for flights (which would be the largest table), they can use `icao` and their departure/arrival datetime (which the table would need to model anyway) to effectively link to the other tables.