Identifying a Surrogate Key

Question

I am modeling as practice an Airline Agency. I have a table "Passenger"

CREATE TABLE Passenger 
(
     confirmationNum INTEGER NOT NULL,
     flightNum       INTEGER NOT NULL,
     seatNum         INTEGER NOT NULL,
     name            VARCHAR(30) NOT NULL,
     phone           VARCHAR(10) NOT NULL
);

If I am correct I would say a passengers confirmation number and the flight number are surrogate keys. What I am wondering is an attribute such as seatNum in this case would also be a surrogate key or would that be considered a natural key.

score 1 · Answer 1 · answered Sep 14 '16 at 06:01

1

I disagree - a surrogate key is an artificially introduced key - typically just one column - that has no business meaning.

However, both flightNum and confirmationNum do have business meaning here in your model. If you use either of the two (or both together) as a key, then you're using a natural key.

A surrogate key would be a PassengerID INT that would be introduced that doesn't have any further business meaning other than uniquely identifying each passenger within the IT system (but not "in the real world").

answered Sep 14 '16 at 06:01

marc_s

732,580
175
1,330
1,459

SeatNum surely has business meaning to the airline selling (or over-selling) space on the specified flight. In the bigger picture it is likely a foreign key identifying unique seating space on a specific plane. – Anthony Simon Mielniczuk Apr 07 '18 at 12:26

score 0 · Answer 2 · answered Sep 15 '16 at 09:07

SeatNum is not any kind of key. A seat is a seat is a seat. That is, there is no differentiation among seats. Even concepts such as "aisle seat" and "window seat" does not derive from any attribute of the seat itself. Within a flight, the Seatnum value must be unique, but such limited uniqueness is hardly sufficient to make it a candidate for keyness.

As you say this is practice, please permit a few more comments. Your table name suggests that the table contains a listing of passengers, yet ConfirmationNum, FlightNum and SeatNum describe, not a passenger, but a many-to-many relationship between a passenger and a flight (or trip). A flight can consist of many passengers and a reservation number can refer to a trip of one or many legs of flights.

So the fields ConfirmationNum, FlightNum and SeatNum would most logically be found in an intersection table like this one:

create table Trip(
  ConfirmationNum  int not null,
  FlightNum        int not null
    references Flights( ID ),
  SeatNum          int not null,
  PassengerNum     int not null
    references Passengers( ID )
  -- Possible other attriutes such as price and departure date
);

The Passengers table would consist of passenger data that would not vary from flight to flight or trip to trip.

A confirmation number could well refer to several different passengers -- a family or football team traveling together -- so the primary key of this table would be a composite key consisting of all four fields as shown.

Also, while it is true that a surrogate key should have no business meaning applied to it, it is also true that this rule is widely ignored. You have a perfectly good unique identifier so why not call it "confirmation number" or "account number" or any other of a wide variety of significant names?

Identifying a Surrogate Key

2 Answers2