has_one with more than one possible foreign key column

Question

I have a Family class that includes a mother_id and a father_id. From the Family model's perspective, it's important to know which parent is the mother and which is the father, but the mother and father as Residents have all the same attributes (i.e. database columns). So ideally, I'd like my model files to look like this:

class Resident < ActiveRecord::Base
  has_one :family, :dependent => :nullify, :foreign_key => :father_id
  has_one :family, :dependent => :nullify, :foreign_key => :mother_id
  attr_accessible :email, :cell, :first_name, :last_name
end

class Family < ActiveRecord::Base
  belongs_to :father, :class_name => 'Resident', :foreign_key => 'father_id'
  belongs_to :mother, :class_name => 'Resident', :foreign_key => 'mother_id'
  attr_accessible :address, :city, :state, :number_of_children
end

This doesn't work. my_family.mother and my_family.father work, so Rails seems to be happy with the double belongs_to. However, my_dad.family == nil, indicating that the second has_one is overriding the first. This is reasonable, because otherwise, what would happen if a resident_id showed up in both the mother_id and father_id columns? (While I plan to add model-level validation to ensure that never happens, has_one doesn't talk to validation methods.) Furthermore, what would my_dad.family = Family.new mean? How would ActiveRecord choose whether to insert my_dad.id into Family.mother_id or Family.father_id?

From this Stackoverflow question, I got the idea to use different names, i.e. change the has_one lines to:

has_one :wife_and_kids, :class_name => 'Family', :dependent => :nullify, :foreign_key => :father_id
has_one :husband_and_kids, :class_name => 'Family', :dependent => :nullify, :foreign_key => :mother_id

My questions are:

1) Is there a better way to do it? A different DB schema, perhaps?

2) Is database-level validation possible to supplement the model-level validation to ensure that my_dad.id can't show up in both the mother_id and father_id columns?

3) Can you think of better names than husband_and_kids / wife_and_kids? (Admittedly not a programming question...)

EDIT: It occurred to me to add a family getter:

def family
  @family ||= self.wife_and_kids || self.husband_and_kids
end
after_save :reset_family
def reset_family
  @family = nil
end

This makes it syntactically cleaner (since I really wasn't a fan of [husband|wife]_and_kids), without creating any ambiguity since there's no setter.

Answer 1

The main issue you're facing is that you have a "conditional" foreign key, meaning the foreign key used to resolve the :family of a resident depends on whether the resident is a male or female (mother or father). The best way to deal with this in my opinion is to use STI (Single-Table Inheritance) to differentiate between the two cases.

class Resident < ActiveRecord::Base
    attr_accessible :email, :cell, :first_name, :last_name
end

class Mother < Resident
    has_one :family, :dependent => :nullify, :foreign_key => :mother_id
end

class Father < Resident
    has_one :family, :dependent => :nullify, :foreign_key => :father_id
end

You can still use the Resident table, but you'll need to migrate a :type field of type string and store the value "Mother" or "Father" depending on the case. Also, place each of these class definitions in its own file in models/.

Edit: I think this also resolves the issues suggested in your second and third questions.

Edit2:

Given the current schema, you would need to create a check constraint on your families table. For one, active record doesn't have direct support for this, so you would have to execute raw sql to add the constraint. In theory, each time a value is added or changed in the "mother_id" column of "families", the check would have to cross reference with the "residents" table, ascertaining that the "type" column of the "resident" is "Mother." The SQL that would (theoretically) add this constraint is

ALTER TABLE families
ADD CONSTRAINT must_be_mother CHECK ((SELECT type FROM residents WHERE residents.id = families.mother_id) = 'Mother')

The problem is that this CHECK contains a subquery, and as far as I know, subqueries in checks are disallowed by many databases. (See this question for specifics).

If you really want to implement a database-level validation here, you will likely need to change the schema by separating "residents" into "mothers" and "fathers."