Professional Ruby on Rails™

6.4. Building a Relationship across Multiple Databases

At this point, you've successfully integrated the data from the external Ingredientopedia database into Soups OnLine. The next thing you're probably going to want to do with the data is allow the preexisting ingredient objects to have a Rails-mediated relationship with the external IngredientData table. Trying to create this relationship exposes another way in which you leave the reservation when trying to support a legacy database alongside the Rails database.

Normally, Rails manages a many-to-many relationship through a series of SQL JOIN calls to the database that retrieve the related data. The problem, of course, is that SQL does not have the magical powers needed to join two tables from two separate databases. However, with a little bit of extra plumbing behind the scenes, it is possible to make the relationship across the database-divide almost as transparent as a typical Rails relationship.

This section applies only to Rails relationships that rely on SQL JOIN, which is to say : has_and_belongs_to_many and :has_many :through. One-to-one relationships like :belongs_to and :has_one do not use JOIN calls and can be managed seamlessly by Rails across multiple databases.

The key to making the join relationships work across multiple databases is to place a proxy table in the Rails database that maps to items in the legacy database. Items in the Rails database can participate in many-to-many relationships with the proxy object, ...