Rationale behind not having support for foreign keys with multiple columns

rubyonrails-talk
8

You’ve gone one step too far. You don’t need to have two different tables to manage the two kinds of relationships. One of Rails’ primary objectives is to satisfy the Single Responsibility Principle. The Apartment already knows what Building it is in, and that fact is not likely to change. So instead of having a separate object to manage the relationship between the building and the maintenance person, simply ask the apartment which building it is in, if you need to know that.

Apartment
  belongs_to :building 
  has_many :leases
  has_many :tenants, through: :leases
  has_many :assignments
  has_many :maintenance_people, through: :assignments

Assignment
  belongs_to :apartment 
  belongs_to :maintenance_person

Building
  has_many :apartments
  has_many :maintenance_people, through: :apartments
  has_many :leases, through: :apartments
  has_many :tenants, through: :leases

Lease
  belongs_to :tenant
  belongs_to :apartment

MaintenancePerson
  has_many :assignments
  has_many :apartments, through: :assignments
  has_many :buildings, through: :apartments

Tenant
  has_many :leases
  has_many :apartments, through: :leases

So now you can get the building from the apartment with @apartment.building or the assignment with @assignment.apartment.building. No need for a different table to manage duplicate data. You’ve stored the data in the most specific manner possible, and you don’t have to update two tables if you want to change a maintenance person’s assignment to a different apartment.

Walter