using custom foreign keys in associations

Hi
I have following models

class Room < ActiveRecord::Base
  belongs_to :roomtype, :foreign_key=>'t_name'
end

class Roomtype < ActiveRecord::Base
  has_many :rooms
  accepts_nested_attributes_for :rooms
end

and the following tables

class CreateRoomtypes < ActiveRecord::Migration
    create_table :roomtypes do |t|
      t.string :t_name
      t.string :rate
      t.string :comment

      t.timestamps
    end
end
class CreateRooms < ActiveRecord::Migration
    create_table :rooms do |t|
      t.string :name
      t.boolean :avail
      t.string :type_name - to reference Roomtype's name

      t.timestamps
    end
  end

I want to use type_name column of Rooms as foreign key to Roomtype
model. I tried running below queries but in vein. What am I doing
wrong? How to 'plumb these together'?

ruby-1.9.2-p0 > Roomtype.first
=> #<Roomtype id: 1, t_name: "type1", rate: "100", comment: nil,
created_at: "2010-10-19 05:33:50", updated_at: "2010-10-19 05:33:50">

ruby-1.9.2-p0 > Room.first
=> #<Room id: 1, name: "201", avail: true, created_at: "2010-10-19
05:33:50", updated_at: "2010-10-19 05:44:32", type_name: "type1">

ruby-1.9.2-p0 > Room.first.roomtype
=> nil
--i was expecting type1 here since the foreign key was t_name which
has type1 value.

I found I needed to use :primary_key => 'type_name' in the room model.
I come from DB world and I know a primary key is one that shouldn't be
duplicated, so I am confused why I am telling the foreign key as
primary_key in rails and the reference column in the parent as
foreign_key.

I found I needed to use :primary_key => 'type_name' in the room model.
I come from DB world and I know a primary key is one that shouldn't be
duplicated, so I am confused why I am telling the foreign key as
primary_key in rails and the reference column in the parent as
foreign_key.

Normally in a rails association the foreign key for a belongs to
always refers to the other table's primary key. If you want it to
refer to a different column then, as you discovered, you use the
primary_key option.

Fred

I think here room's(associator for belongs_to) primary_key refers to
room_type(associated) foreign_key. This was why I got confused. If the
code was like
class room
  belongs_to :room_type, foreign_key => 'type_name', refers_to =>
't_name' #(t_name - column in room_type class)
end

This would've been cleaner to think that the room's type_name refers
to room_type's t_name column.
Anyway.. me just getting to know if you don't do it the rails way, its
going to be tough way....

Arun Srini wrote in post #955342:

class CreateRoomtypes < ActiveRecord::Migration
    create_table :roomtypes do |t|
      t.string :t_name
      t.string :rate
      t.string :comment
      t.timestamps
    end
end
class CreateRooms < ActiveRecord::Migration
    create_table :rooms do |t|
      t.string :name
      t.boolean :avail
      t.string :type_name - to reference Roomtype's name

      t.timestamps
    end
  end

I want to use type_name column of Rooms as foreign key to Roomtype
model. I tried running below queries but in vein. What am I doing
wrong? How to 'plumb these together'?

Since you do seem to be defining the rooms table, why not use the rails
'traditional' roomtypes_id field as your key? It is much clearer (I
know what that field is, and I know where it connects to), it is the
'convention over configuration' solution, and it alleviates any
potential issue with someone in the future deciding that "Oh, that room
type name doesn't really fit anymore, can I change it?" - I personally
abhor descriptive strings as keys for precisely that reason...

Leave some of that DB world training behind and embrace the 'rails way',
you'll be much happier, and things tend to fit more naturally.

Your argument was very fine, and I accept that. I am a hardcore DBA
and SQL expert, with good data modelling experience, and have come
across many models that needs to have multiple keys as prmary and
foreign. Thus my quest to custom foreign keys.
Anyway, the 'magic' rails does is very interesting..

Arun Srini wrote in post #955420:

Your argument was very fine, and I accept that. I am a hardcore DBA
and SQL expert, with good data modelling experience,

If that were true, then you'd know that your original schema is not
normalized to 3NF and should not be used for general purposes.

and have come
across many models that needs to have multiple keys as prmary and
foreign. Thus my quest to custom foreign keys.

You don't need custom foreign keys for this. Just normalize your DB
schema properly and you'll most likely get something that Rails can work
with.

Anyway, the 'magic' rails does is very interesting..

Best,

Arun Srini wrote in post #955420:

> Your argument was very fine, and I accept that. I am a hardcore DBA
> and SQL expert, with good data modelling experience,

If that were true, then you'd know that your original schema is not
normalized to 3NF and should not be used for general purposes.

3NF - every non prime attribute int he table should be directly
dependent on candidate key. I have room_type that is not directly
dependent on key(or even storing the motel info in central table), and
thus it's not in 3rd NF. I understand that , no need of any personal
comments here.

> and have come
> across many models that needs to have multiple keys as prmary and
> foreign. Thus my quest to custom foreign keys.

You don't need custom foreign keys for this. Just normalize your DB
schema properly and you'll most likely get something that Rails can work
with.

I don't need custom foreign keys here, all I was tellign was that
there have been some cases where I needed more than one column to
refer to parent column and wanted to do this in rails.

I have been up all night trying to solve this same problem, and I
finally got it to work.

I'll use your tables to demonstrate what I did::