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.
on 2008-10-29 22:19
on 2008-10-29 22:24
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
on 2008-10-29 22:29
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.
on 2008-10-30 00:59
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.
on 2008-10-30 01:25
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.
on 2008-11-13 00:49
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?