# I need a new type of polymorphic has\_many association with dynamic foreign\_key

**URL:** <https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524>\
**Category:** Rails\
**Created:** [April 8, 2010, 10:04am UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524 "2010-04-08T10:04:52Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 8, 2010, 10:04am UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/1 "2010-04-08T10:04:52Z")

</div>

A vanilla polymorphic belongs\_to association allows different  
association instances to have targets in different classes. I want to  
do a has\_many association where different association instances have  
different foreign\_keys. Is such a thing possible?

My schema looks like

create\_table “people”, :force =\> true do |t|  
t.string “name”  
t.string “sex”  
t.integer “father\_id”  
t.integer “mother\_id”  
end

and my model I want something like:

class Person \< ActiveRecord::Base  
belongs\_to :father, :class\_name =\> “Person”  
belongs\_to :mother, :class\_name =\> “Person”

#I want something like this is invalid syntax  
has\_many :children, :class\_name =\> “Person”, :foreign\_key =\> ( sex  
== ‘M’ ? :father\_id : :mother\_id )  
end

Will it be hard to create an association that can handle this?

Thanks in advance for any advice you can give me,  
Joe

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 8, 2010, 10:55am UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/2 "2010-04-08T10:55:48Z")

</div>

On 8 April 2010 08:07, Joe [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> class Person \< ActiveRecord::Base  
> belongs\_to :father, :class\_name =\> “Person”  
> belongs\_to :mother, :class\_name =\> “Person”

What about children with two mums or two dads? Or the more frequent  
occasion where there’s a mum, dad and one or more step-parents? How  
about children looked after by their grandparents?

You might be better off (and it would make your example query easier  
too) if you removed the foreign keys from people, and had a  
relationship like:

class Person \< ActiveRecord::Base  
has\_many :parents  
end

create\_table “parents” do |t|  
t.string “relationship” # mother/father/stepmother/etc  
t.integer “child\_id”  
t.integer “person\_id”  
end

class Parent \< ActiveRecord::Base  
belongs\_to :child, :class\_name =\> “Person”  
belongs\_to :person # this is the link for the “parent” person  
end

If you _have_ to only have one mother and father and nothing else, my  
suggestion lets you do that by putting an index on parents across  
relationship and child\_id to ensure only one of each, and validate  
that only mother/father is put into the relationship field.

Then, including a relationship like:  
has\_many :children, :through =\> “parents”, :class\_name =\> “Person”

… gives you Person.first.children

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 8, 2010, 12:58pm UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/3 "2010-04-08T12:58:45Z")

</div>

On 8 April 2010 08:07, Joe [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> My schema looks like
> 
> create\_table “people”, :force =\> true do |t|  
> t.string “name”  
> t.string “sex”  
> t.integer “father\_id”  
> t.integer “mother\_id”  
> end

PS Here’s a couple of links to DB designs for family relationships:  
[http://www.familyhistorysa.info/dbDesign.html](http://www.familyhistorysa.info/dbDesign.html)  
[http://www.databaseanswers.org/data\_models/genealogy/index.htm](http://www.databaseanswers.org/data_models/genealogy/index.htm)

And here’s one for modelling marriages (not strictly what you’re  
talking about, but the principle of husband/wife not being as simple  
as first thought is similar to your issue with mother/father):

> **[Gay marriage: the database engineering perspective @ Things Of Interest](https://qntm.org/gay)**
>
> Update, 2017-09-25
> 
> Hi! This essay is undergoing a long-overdue rewrite. In the meantime, consider my 2015 follow-up essay, Obergefell v. Hodges: the database engineering perspective....

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 9, 2010, 5:24am UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/4 "2010-04-09T05:24:43Z")

</div>

Joe wrote:

> A vanilla polymorphic belongs\_to association allows different  
> association instances to have targets in different classes. I want to  
> do a has\_many association where different association instances have  
> different foreign\_keys. Is such a thing possible?
> 
> My schema looks like
> 
> create\_table “people”, :force =\> true do |t|  
> t.string “name”  
> t.string “sex”  
> t.integer “father\_id”  
> t.integer “mother\_id”  
> end

I believe that you need a generic relationship model… the vagaries of  
people’s interactions/behaviors/affairs (ha!) leads to relationship  
instances like:

person\_1 person\_2 relationship (person\_1 is the what of person\_2)

* * *

Person A Person B spouse  
Person A Person C ex-spouse  
Person D Person A child  
Person D Person B stepchild  
Person D Person C child  
etc, etc, etc.

More work up front, but eminently more flexible.

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 9, 2010, 6:35pm UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/5 "2010-04-09T18:35:25Z")

</div>

I think you can accomplish what you need with :has many :through.  
You’ll need another table to store the relationships as Ar Chron  
suggested.

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 9, 2010, 11:00pm UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/6 "2010-04-09T23:00:43Z")

</div>

Hi Michael-

Thanks so much for the reply. Those links were very helpful, and I  
especially enjoyed the one about gay marriage.

So it seems like it would be very difficult to do what I originally  
wanted (create a new kind of association that works in a new way), and  
it would require an understanding of rails internals and a lot of  
work. The upshot is that if I think I need to change rails, I’m  
probably wrong, and I just need to change my schema to conform to best  
practices.

So I will take the advice, and alter the schema to fit with rails  
better. And following tips I got from those articles, it will have  
more flexibility too, though I think it will also have much more  
complexity than I had originally expected. I’m still nailing down my  
perfect schema, but I think I’m getting close.

Thanks for your advice  
Joe

---

<div class="post-metadata">

**Author:** ![joe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/b9e5f3/32.png) [@joe](https://www.ruby-forum.com/u/joe)\
**Post date:** [April 10, 2010, 6:47am UTC](https://www.ruby-forum.com/t/i-need-a-new-type-of-polymorphic-has-many-association-with-dynamic-foreign-key/186524/7 "2010-04-10T06:47:18Z")

</div>

I found a way to make my original schema work. Single table  
inheritance. Define the associations on the subclasses, and you can  
use a different foreign key in each subclass. Works like a charm.

I’m still debating with myself about a more sophisticated schema like  
the ones suggested though.