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 -----------idflight_numberPassengers--------idnamePassengerFlights---------------passenger_idflight_idFor 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_idpassenger_flight_idOption #2
MoviePassengerFlights----------------------movie_idpassenger_idflight_idThe 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.