# Foreign key question

**URL:** <https://www.ruby-forum.com/t/foreign-key-question/152079>\
**Category:** Rails\
**Created:** [October 29, 2008, 10:19pm UTC](https://www.ruby-forum.com/t/foreign-key-question/152079 "2008-10-29T22:19:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![russianbandit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e36b37/32.png) [@russianbandit](https://www.ruby-forum.com/u/russianbandit)\
**Post date:** [October 29, 2008, 10:19pm UTC](https://www.ruby-forum.com/t/foreign-key-question/152079/1 "2008-10-29T22:19:23Z")

</div>

Here’s my situation:

Let’s say, I have a “members” table that tools like this:

id | member\_id | name |

and I have a “invitation” table that looks like this:

id | member\_id | date |

In my case, invitation.member\_id needs to be the key to reference  
members.member\_id

What do I need to do in my models so I can do things like:

member.invitation.date

I’ve tried doing:

class Member  
has\_one :invitation, :foreign\_key =\> “member\_id”  
end

class Invitation  
belongs\_to :member, :foreign\_key =\> “member\_id”  
end

Any ideas?

Thanks.

---

<div class="post-metadata">

**Author:** ![russianbandit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e36b37/32.png) [@russianbandit](https://www.ruby-forum.com/u/russianbandit)\
**Post date:** [October 29, 2008, 10:29pm UTC](https://www.ruby-forum.com/t/foreign-key-question/152079/2 "2008-10-29T22:29:44Z")

</div>

Maurício Linhares wrote:

> Why your members table have a column called member\_id?
> 
> - 
> 
> Maur�cio Linhares  
> [http://alinhavado.wordpress.com/](http://alinhavado.wordpress.com/) (pt-br) | [http://blog.codevader.com/](http://blog.codevader.com/)  
> (en)  
> Jo�o Pessoa, PB, +55 83 8867-7208
> 
> On Wed, Oct 29, 2008 at 6:19 PM, Greg L.

I need to keep that around, because it’s a unique field, but when  
looking at the table the member\_id means more to us than looking at id.

---

<div class="post-metadata">

**Author:** ![russianbandit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e36b37/32.png) [@russianbandit](https://www.ruby-forum.com/u/russianbandit)\
**Post date:** [October 29, 2008, 10:24pm UTC](https://www.ruby-forum.com/t/foreign-key-question/152079/3 "2008-10-29T22:24:01Z")

</div>

Why your members table have a column called member\_id?

- 

Maurício Linhares  
[http://alinhavado.wordpress.com/](http://alinhavado.wordpress.com/) (pt-br) | [http://blog.codevader.com/](http://blog.codevader.com/)  
(en)  
João Pessoa, PB, +55 83 8867-7208

On Wed, Oct 29, 2008 at 6:19 PM, Greg L.

---

<div class="post-metadata">

**Author:** ![russianbandit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e36b37/32.png) [@russianbandit](https://www.ruby-forum.com/u/russianbandit)\
**Post date:** [October 30, 2008, 1:25am UTC](https://www.ruby-forum.com/t/foreign-key-question/152079/4 "2008-10-30T01:25:28Z")

</div>

I think ActiveRecord will expect this join condition to work:

on members.id = invitation.member\_id

Would that work on your data?

I think that in both of those association declarations, the :foreign\_key  
argument names the field in the child table that should be linked to the  
pk of the parent (is that right?). So if you want member\_id to be the  
pk of the members table, you need to add a primary\_key declaration on  
your members class (in which case you should probably ditch the  
members.id field, as it’s liable to cause confusion).

I’d either do that, or rename members.member\_id to something like  
members.legacy\_member\_id. That would keep you clear of the rails  
conventions.

---

<div class="post-metadata">

**Author:** ![russianbandit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e36b37/32.png) [@russianbandit](https://www.ruby-forum.com/u/russianbandit)\
**Post date:** [October 30, 2008, 12:59am UTC](https://www.ruby-forum.com/t/foreign-key-question/152079/5 "2008-10-30T00:59:58Z")

</div>

First, you never have a foreign key on both sides of a relationship.  
That makes no sense to a RDBMS.

> Let’s say, I have a “members” table that tools like this:
> 
> id | member\_id | name |
> 
> and I have a “invitation” table that looks like this:
> 
> id | member\_id | date |
> 
> class Member  
> has\_one :invitation, :foreign\_key =\> “member\_id”  
> end
> 
> class Invitation  
> belongs\_to :member, :foreign\_key =\> “member\_id”  
> end

Second, I would highly recommend renaming your “member\_id” column in you  
“members” table. Appending “\_id” to a column means it is a foreign key  
in Rails naming conventions. Use something like “member\_number” or  
“member\_identifier.” This is both a courtesy for other developers  
looking at your model and for your own sanity later. I’m assuming this  
value is a unique number generated by your code when creating a new  
member. Not sure why the “id” column wouldn’t work just as well. But,  
hey, it’s your design. In any case you “member\_id” column is NOT a  
foreign key. It is a “secondary” unique value but it’s not a key field.

Third, You are breaking the First Normal Form (1NF) by storing a  
member’s name in a single field. Assuming this is a person their name is  
composed of First Name and Last Name. 1NF says to separate separate data  
elements into separate columns (no composite fields). This is not  
absolute. It’s possible your member names are companies or something. In  
which case you would not have a composite field.

So, that leave us with the following design, when following Rails  
conventions:

Member:  
id | member\_number | first\_name | last\_name

Invitation:  
id | member\_id | date

class Member \<\< ActiveRecord::Base  
has\_one :invitation  
end

class Invitation \<\< ActiveRecord::Base  
belongs\_to :member  
end

No need to specify the foreign key since Rails conventions are being  
followed.

This is also assuming that each member can only have one associated  
invitation since it is describing a one-to-one association.

---

<div class="post-metadata">

**Author:** ![russianbandit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/e36b37/32.png) [@russianbandit](https://www.ruby-forum.com/u/russianbandit)\
**Post date:** [November 13, 2008, 12:49am UTC](https://www.ruby-forum.com/t/foreign-key-question/152079/6 "2008-11-13T00:49:43Z")

</div>

Thanks for explaining the \_id meaning. I didn’t know that that’s how  
Rails associates tables.

> So, that leave us with the following design, when following Rails  
> conventions:
> 
> Member:  
> id | member\_number | first\_name | last\_name
> 
> Invitation:  
> id | member\_id | date
> 
> class Member \<\< ActiveRecord::Base  
> has\_one :invitation  
> end
> 
> class Invitation \<\< ActiveRecord::Base  
> belongs\_to :member  
> end

However, in my case invitations.member\_id needs to be  
invitations.member\_number and it should reference the  
members.member\_number. Here’s why - we do a lot of MYSQL lookup and if  
we quickly want to glance thought the invitations table, we can look  
member\_numbers and tell who received invitations. The field  
member\_number (previously member\_id) comes from a legacy database and to  
us it is a lot more “readable” than ID. Does that make sense? Should we  
have done something better?

So the condition we’re looking for here is:

on members.member\_number = invitation.member\_number

Should we just make member\_number our primary key?