has_many through join table - join three tables?

Hi

Is it feasible or horribly wrong to include a boolean column to a join table in order to 'junction' a join?

Imagine you want to map a journey that will comprise multiple flights and train journeys.

# train.rb # flight.rb # stages.rb # itinerary.rb

Itinerary has many flights [b]or[/b] trains journeys through stages.

I could put a column for each id, train and flight, or I can put a boolean flag that dictates whether the stage is a flight or train journey and define in the model the foreign key in both cases. In the case of the former, there will be a lot of null values so the latter seems preferable. Maybe an answer is a further join table, but for the purpose of this that is really not ideal.

So will this work ok or am I barking up the wrong tree? Thanks in advance. :slight_smile:

Have you considered using Single Table Inheritance so that train and flight are two types of stages? Then an itinerary has many stages, each of which is a flight or a train, or boat or whatever.

Colin

Hi Colin. Unfortunately in this case sti isn't feasible. Sorry, I should have pointed out that the scenario provided is a simplified demonstration.

It occurs to me that this is a polymorphic has_many_through which led me to this article:

http://blog.hasmanythrough.com/2006/4/3/polymorphic-through

It's a few years old but seems applicable.

Advice and experiences still very welcome. :slight_smile: