thr3ads.net - Rails - Foreign key question [Oct 2008]

If this information is useful, please help other people find it:
Share via:

Greg Lazarev

2008-Oct-29 21:19 UTC

Foreign key question

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Maurício Linhares

2008-Oct-29 21:23 UTC

head link

Re: Foreign key question

Why your members table have a column called member_id?

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



On Wed, Oct 29, 2008 at 6:19 PM, Greg Lazarev
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
> 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.
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Greg Lazarev

2008-Oct-29 21:29 UTC

head link

Re: Foreign key question

Maurício Linhares wrote:> Why your members table have a column called member_id?
> 
> -
> Maur�cio Linhares
> http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/ 
> (en)
> Jo�o Pessoa, PB, +55 83 8867-7208
> 
> 
> 
> On Wed, Oct 29, 2008 at 6:19 PM, Greg Lazarev
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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Robert Walker

2008-Oct-29 23:59 UTC

head link

Re: Foreign key question

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Pardee, Roy

2008-Oct-30 00:24 UTC

head link

Re: Foreign key question

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.

-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Greg Lazarev
Sent: Wednesday, October 29, 2008 2:19 PM
To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
Subject: [Rails] Foreign key question


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.
--
Posted via http://www.ruby-forum.com/.



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Greg Lazarev

2008-Nov-12 23:49 UTC

head link

Re: Foreign key question

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?
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Oct 2008 - Foreign key question

Foreign key question

Re: Foreign key question

Re: Foreign key question

Re: Foreign key question

Re: Foreign key question

Re: Foreign key question