Lets say we have Passengers, where Passengers can attend many Flights, and one Flight may have many Passengers. In each Flight, each Passenger has a unique selection of Movies he/she can watch.
For Passengers, Flights, and the association between them, we have three tables:
Flights ----------- id flight_number Passengers -------- id name PassengerFlights --------------- passenger_id flight_id
For Movies, it must specify for which Passenger and for which Flight will it be available to.
In this case, is it fine to reference the PassengerFlights junction table with a foreign key, or would it be better to create a junction table with three foreign keys(passenger_id, flight_id, movie_id)? For example:
Option #1
MoviePassengerFlights --------------------- movie_id passenger_flight_id
Option #2
MoviePassengerFlights ---------------------- movie_id passenger_id flight_id
The concern I’m having with Option #2, which seems the most straightforward, is that it remains a valid record even if the relationship between a Passenger and Flight gets deleted.
Would Option #1 or Option #2 be recommended for such use cases? Or is there a better alternative?
Advertisement
Answer
junction/join table
In the Relational paradigm, there is no such thing. The term is Associative table. It associates two parent tables, and resolves a many-to-many relationship between them. JOIN is an SQL verb.
The concern I’m having with Option #2, which seems the most straightforward, is that it remains a valid record even if the relationship between a Passenger and Flight gets deleted.
That is false. If “the relationship between a Passenger and Flight gets deleted”, the Passenger is no longer on that Flight, therefore there is no Flight-Passenger for which the Passenger can make reservations for Movies.
Worse still, if the relationship between a Passenger and Flight is not deleted, you can reserve Movies for any old Passenger for any old Flight … which is incorrect: Movie reservations should be constrained to Passengers who are actually on a specific Flight, a PassengerFlight.
I do not see how that is at all “straight-forward”.
Would Option #1 or Option #2 be recommended for such use cases?
For your declared files:
- Option 1 is correct.
- Option 2 is grossly incorrect.
That is also the generic answer for such “use cases”.
Is it fine to have a foreign key referencing an Associative table?
The answer is yes. It is very common in Relational databases, once you get past a few tables. Each table is a Fact, each relationship is one between two Facts. An Associative table is just a Fact, which can be referenced by any subordinate Fact.
Or is there a better alternative?
Yes. A Relational database. Your declared files are not Relational, they are a typical 1960’s, pre-relational, Record Filing System, deployed in SQL for convenience. That is not entirely your fault: the great mass of “literature”; all the textbooks, that are marketed by “theoreticians” and sundry authors, that purport to be “relational” are in fact anti-relational. What they labour over is what they understand: 1960’s, pre-relational RFS, “relational” only in name. It is a massive tragedy. Relational databases have far more Integrity; Power; and Speed.
That is beyond the scope of the Question. Feel free to ask another Question, and I will answer it.