How do I handle many NULL-able foreign keys in Postgres?

Question

I hope to make a table of routes that points to a table of flights.

But, the there can be as many as 25 flights per route and I want every flight to be a foreign key.

It seems very wasteful to make a table like this:

CREATE TABLE routes (
id SERIAL PRIMARY KEY,                                                                              
flight1 INT references "flights" (id),
flight2 INT references "flights" (id),
...
flight24 INT references "flights" (id),
flight25 INT references "flights" (id),
rating INT NOT NULL
);

Since, the average number of flights should be around 8. I will just fill the empty flights with NULLs. So the average route will contain 17 NULLs.

Is this the correct way? I looked into arrays of foreign keys of arbitrary length, but those do not seem to be supported on psql (9.3.10)

Answer 1

This is not the correct way. You need an additional table. So, something like this:

CREATE TABLE routes (
    RouteId SERIAL PRIMARY KEY,
    Rating int not null
);

CREATE TABLE RouteFlights (
    RouteFlightId serial primary key,
    RouteId int not null references Routes(RouteId),
    Flight int not null references Flights(FlightId)
);