shared primary key (using the primary key as a foreign key for has_one association)

[Alexey M.]

Hello, can anyone simply tell me if what i want to do is ok to do with
Rails, or will there arise some serious obstacle?
If it is ok to do, then i will probably figure out a way ofter some
trial and error.
Any other suggestion will also be appreciated.

I want to share the primary key among several tables, and then to also
use it as the foreign key for has_one associations.

I have models "Person", "Instructor", and "Member" (tables "people",
"instructors", and "members").
Instructors and members of an association are people, so i plan to store
their personal information in "people" table, and to store a foreign key
"person_id" in "instructors" and "members" tables.
I also plan to have the associations:

class Person < ActiveRecord::Base
has_one :instructor, :dependent => :destroy
has_one :member, :dependent => :destroy
end

class Instructor < ActiveRecord::Base
belongs_to :person
end

class Member < ActiveRecord::Base
belongs_to :person
end

It is possible for a same person to be an instructor and a member in the
same time, or neither of two.

Now i do not need "id" in "instructors" and "members" tables, as i can
use "person_id" as the primary key.
I plan to create the tables with migrations like these:

class CreateInstructors < ActiveRecord::Migration
def self.up
create_table :instructors, :primary_key => "person_id" do |t|
t.text :presentation
t.binary :photo
...
end
end
...
end
...
class CreateMembers < ActiveRecord::Migration
def self.up
create_table :members, :primary_key => "person_id" do |t|
t.date :member_since
...
end
end
...
end

My question is: is this going to work, or will i encounter some
unresolvable issues?
(For example, when creating a new Instructor, how to specify its primary
key "person_id" to be equal to the "id" of an existing person?)

Thanks.

Alexey.

--
Posted via http://www.ruby-forum.com/.

[Colin Law]

On 25 March 2011 16:01, Alexey M. <li...@ruby-forum.com> wrote:
> Hello, can anyone simply tell me if what i want to do is ok to do with
> Rails, or will there arise some serious obstacle?
> If it is ok to do, then i will probably figure out a way ofter some
> trial and error.
> Any other suggestion will also be appreciated.
>
> I want to share the primary key among several tables, and then to also
> use it as the foreign key for has_one associations.

Why do you want to use the same primary key value for multiple tables?
Unless you have a really good reason for not following the rails
conventions then it is best to stick to them. Your life will be much
easier. Just set up the appropriate relationships between the models
in the normal way.

Colin

[Alexey M.]

Colin, probably my life will be much easier, but much less fun.
It is just so natural to use the same primary key in this situation.

[Colin Law]

On 25 March 2011 16:47, Alexey M. <li...@ruby-forum.com> wrote:
> Colin, probably my life will be much easier, but much less fun.

Are you sure? Trying to force rails away from its preferred
conventions is rarely fun. Do it the easy way and use the time saved
to indulge in something that is really fun :)

> It is just so natural to use the same primary key in this situation.

Why?

Colin

[Kendall Gifford]

It is certainly "possible" to do what you're describing, and yes, the same person *could* be both an instructor and a member at the same time.

However, I'll second Colin's suggestion that you just follow the convention to have a separate id primary key column in both the instructors and members tables. The extra bytes-per-record in your database will be worth the lack of hassle, even if you grow to many millions of records.

Now, if you insist on doing it the way you describe, you'll need to use the #set_primary_key macro method in your models:

class Instructor
  set_primary_key "person_id"
  ...
end

You'll also need to be sure to explicitly set the primary key value when creating your instructor and member records. If you do this through your has_one relationship (person.create_instructor for example) it *may* do this for you. I don't know for sure, never tried this. Test it out and see.

Likewise, I'm guessing that your relationship definitions (since you're using the "person_id" name) won't need any tweaking. But again, test and see (to be sure you won't need to provide :primary_key and/or :foreign_key options to your belongs_to and/or has_one relationship definitions).

[Alexey M.]

>> It is just so natural to use the same primary key in this situation.
>
> Why?

Well, it seems natural to me, and i often trust my senses :).

I have been playing with it in console for a few minutes now and
observing some fun and weird behavior:

i = Instructor.create
i.id # => 1
i.person_id # => nil
Instructor.find(1) # => NoMethodError: undefined method `eq' for
nil:NilClass ...
i=Instructor.find_by_person_id(1)
i.id # => nil
i.person_id # => 1

So far it seem to be possible to find ways around to make it work, but i
wonder if i am not making a philosophical mistake indeed, and if it will
not break in new versions of rails ...

[Kendall Gifford]

It appears from your console session you never added the #set_primary_key macro method call to you Instructor class. I just duplicated your console session as follows in a test project only *with* the set_primary_key call as follows:

  i = Instructor.create
  i.id # => 1

  i.person_id # => 1
  Instructor.find(1) # => #<Instructor person_id: 1, created_at: "2011-03-25 17:24:04", updated_at: "2011-03-25 17:24:04">
  i = Instructor.find_by_person_id(1) # => #<Instructor person_id: 1, created_at: "2011-03-25 17:24:04", updated_at: "2011-03-25 17:24:04">
  i.id # => 1
  i.person_id # => 1

I just wanted to point this out. I still suggest following rails conventions but rails *is* flexible (I have to flex it to support and old, pre-existing database that does NOT follow the conventions).

[Colin Law]

On 25 March 2011 17:15, Alexey M. <li...@ruby-forum.com> wrote:
>>> It is just so natural to use the same primary key in this situation.
>>
>> Why?
>
> Well, it seems natural to me, and i often trust my senses :).
>
> I have been playing with it in console for a few minutes now and
> observing some fun and weird behavior:
>
> i = Instructor.create
> i.id    # => 1
> i.person_id    # => nil
> Instructor.find(1)    # => NoMethodError: undefined method `eq' for
> nil:NilClass ...
> i=Instructor.find_by_person_id(1)
> i.id    # => nil
> i.person_id    # => 1

This is some strange new use of the word fun, with which I am not familiar.

Colin

[Alexey M.]

Kendall, thank's for your suggestion, it really made "id" and
"person_id" equivalent for instructors and stopped the described above
weird (and fun) behavior.

Also,

p=Person.create
m=p.create_member
i=p.create_instructor

work as expected now with setting identical values for p.id, m.id,
m.person_id, i.id, and i.person_id.

When an instructor with the value of id equal to p.id already exists,
i=p.create_instructor
triggers
ActiveRecord::StatementInvalid: SQLite3::ConstraintException: PRIMARY
KEY must be unique: ...

which is a desired behavior.

I will keep testing, and if i do not encounter something extremely
weird, i may stick to this...

Thank you.

[Alexey M.]

A relevant question that i think is appropriate for this thread: can
anybody please explain to me the purpose of primary_key type in context
of migrations:
t.primary_key :person_id
?

The migration

class CreateInstructors < ActiveRecord::Migration
def self.up

create_table :instructors do |t|
t.primary_key :person_id
t.text :presentation
...

does not work (SQLite3::SQLException: table "instructors" has more than
one primary key: ... during migration),
but

class CreateInstructors < ActiveRecord::Migration
def self.up
create_table :instructors, :primary_key => "person_id" do |t|

t.primary_key :person_id
t.text :presentation
...

and

class CreateInstructors < ActiveRecord::Migration
def self.up
create_table :instructors, :primary_key => "person_id" do |t|
t.text :presentation

...

seem to have completely identical effect:-/.

[Alexey M.]

Just on more related question (if anyone has time to answer):

if i have set_primary_key 'person_id':

class Instructor < ActiveRecord::Base
set_primary_key 'person_id'
...
end

does it mean that writing

class Person < ActiveRecord::Base
has_one :instructor, :primary_key => 'person_id'
end

would be redundant?
What is the purpose of :primary_key in has_one options, if one uses
set_primary_key ?

Thanks.

[Alexey M.]

I have deleted one of my questions because i was apparently confused.
Anyway, i do not understand the use of :primary_key parameter in has_one
option like in

has_one :something, :primary_key => 'someone_id'

What is it for if the primary key has to be set with set_primary_key =>
'someone_id' ?

[Kendall Gifford]

For the situation described in this thread where your Person class's #has_one :instructor refers to another class (and table) that in fact HAS a column named "person_id" then there is no need to provide the :primary_key option, as this is what it will infer by default.

However, in even weirder situations (like mine for instance where I'm connecting ActiveRecord to an old database that doesn't come close to following the rails conventions), it is useful. Example (similar to what I have to deal with):

table: tblUser
primary key: UserID

table: tblComments
primary key: ID # note the inconsistencies I have to deal with
foreign key (to tblUser record): PosterID

Here, "comments" belong to "users" (a user has_many comments). My models look like this:

class User < ActiveRecord::Base
  set_table_name "tblUser"
  set_primary_key "UserID"
  has_many :comments, :primary_key => "UserID", :foreign_key => "PosterID"
end

class Comment < ActiveRecord::Base
  set_table_name "tblCommants"
  set_primary_key "ID"
  belongs_to :user, :primary_key => "UserID", :foreign_key => "PosterID"
end

This is an example when you need to use the :primary_key options (and :foreign_key as well). This is because there is NO way rails can infer these names otherwise. However, despite my schema "sucking" (in rails terms) I manage to otherwise use ActiveRecord quite nicely.

Now, just because this is possible, doesn't mean I'd ever choose to go against conventions. It is beautiful when you can just rely on the defaults and let the rails magic fly!

[Kendall Gifford]

To further clarify (and answer one of the questions above):

You must specify the :primary_key option to #has_one (and #has_many) in your model definition if, and only if, the table of the resource that "owns" the other record (i.e., the table associated w/the model that contains the #has_one method call) doesn't use "id" for its own primary key. The same applies to #belongs_to, :primary_key is only needed when the "owning" resource's table doesn't use "id". So, if a post has many comments and you're hooking the relationships up, as long as the posts table uses "id" for its primary key, then neither the #has_many inside the Post class or the #belongs_to inside the Comment class will need the :primary_key option.

Similarly, you must specify the :foreign_key options to #belongs_to, #has_many and #has_one if and only if the "owned" resource (the one that actually has the foreign key column) doesn't follow the rails convention for its foreign key column name (lower-case and underscored version of the resource's class name, which is ideally the same as the singular version of the table name, all with the "_id" suffix). So, as long as the table backing the Comment class (even if the table has a wonky name and #set_table_name is used) has a "post_id" column then :foreign_key options won't be needed on any of the relationships (on the owner or ownee).

In the OP's situation, the owning table used "id" for its primary key (people table for the Person class). Thus no :primary_key options were needed anywhere. Likewise, the owned tables (instructors for the Instructor class (and the other one...)) used the rails convention for the foreign key names, so :foreign_key options were unnecessary. All this regardless of the fact that the OP *happened* to be re-using the primary key for the foreign key in the owned resource tables.

Now, if one of the owned resources later needed to "own" something themselves (like an instructor having many "students" for example) then *those* relationships would have required :primary_key options since "id" wasn't the primary key name.

Now, to answer one of the questions posed further above...

The :primary_key option in migrations vs. the #primary_key method available on the table definition object inside your #create_table call are, for most part, redundant. Also, when it comes to migrations that create new tables, I personally see no need for both and would just always use the :primary_key option to #create_table.

However, if you created a table long ago and for some reason it had no primary key (imagine an old database or a table that used to just be a join table). You might want to "add" a primary key in a new migration. In this case, you definitely won't be using the :primary_key option to #create_table in your migration. You'll be modifying the table to add a new primary key column.

Now, the presence of the #primary_key method on a table definition would make sense if this worked:

class AddIdToOldItems < ActiveRecord::Migration
  def self.up
    change_table :old_items do |t|
      t.primary_key :id
      # other changes ...
    end
  end
  def self.down
    change_table :old_items do |t|
      # other changes ...
      t.remove :id
    end
  end
end

However, on a quick test, #primary_key isn't defined in this context. Instead, you have to use one of:
    change_table :old_items do |t|
      t.column :id, :primary_key
    end
Or just
    add_column :students, :id, :primary_key

So, I guess having #primary_key available in the context in creating a new table is just for extra options? Anyone else care to comment on this?

[Alexey M.]

Kendall, thank you for your explanations, especially about migrations.

I removed my other post because i realized that in my situation the
primary key used for the association was in fact "id", so it was a bad
example.

Still, in your example, i do not understand the need for ":primary_key
=>":

Kendall Gifford wrote in post #989309:

> class User < ActiveRecord::Base
> set_table_name "tblUser"
> set_primary_key "UserID"
> has_many :comments, :primary_key => "UserID",
> :foreign_key => "PosterID"
> end

was it not working without it?

It seems redundant to write:

class Instructor < ActiveRecord::Base
set_primary_key 'person_id'

has_many :lessons, :primary_key => 'person_id'
...
end

It also does not seem to me to be strictly necessary to add to the
above:

class Lesson < ActiveRecord::Base
belongs_to :instructor, :primary_key => 'person_id'
...
end

However, i can philosophically understand the need for ":primary_key =>"
on the "belongs_to" side of association, it could serve to make this
side more independent from the "has_many" side, and avoid unexpected
behavior of Lesson if Instructor is broken ("set_primary_key
'person_id'" deleted).
It seems to me that Rails makes it possible to define and use only one
side of an association, without defining the other (otherwise the
":inverse_of =>" option probably wouldn't be useful).

I have not properly tested this situation with and without ":primary_key
=>", but so far it seems to work without, "set_primary_key 'person_id'"
seems to suffice for both sides.
So i will try to leave it like this...

P.S. the reason i am torturing Rails like that is that it also has a
convention that it should obey the human.
Seriously, i just want a nice and clear structure of my database, which
would be editable by hand (it is not going to be big), and to use Rails
just for a simple interface.

[Kendall Gifford]

It is indeed redundant to have both #set_primary_key and the :primary_key option w/in your #has_many (or #has_one) calls.

You can safely ignore that aspect of my prior posts :). The real code I yanked my examples from actually has a table with a primary key named "ID" (so set_primary_key "ID") but has another column "UserID" (that is also unique and *could* have been the primary key instead; crazy old db) that for all relationships is treated as if it were the primary key.

So, in reality my model is more like this:

class User < ActiveRecord::Base
  set_table_name "tblUser"

  set_primary_key "ID"

  has_many :comments, :primary_key => "UserID",
    :foreign_key => "PosterID"
end

That's what I get for not simply copy/pasting (then nuking irrelevant details) and hand-coding my example from memory.

Anyhow, your other observation is also dead on. Creating an association on a model doesn't require you to create the "inverse" association on the other model.

> P.S. the reason i am torturing Rails like that is that it also has a
> convention that it should obey the human.
> Seriously, i just want a nice and clear structure of my database, which
> would be editable by hand (it is not going to be big), and to use Rails
> just for a simple interface.

While I always follow rails conventions when possible (and recommend others do too), I also like to "torture" frameworks to see how flexible they are. After all, there are always occasions where it becomes necessary, such as in my instance where I have to work off of a legacy database that is still concurrently being accessed by legacy software. On the side, however, I've instantiated a dozen smaller "toy" or "utility" rails apps where I've followed the conventions and idioms religiously to great effect. Love 'em.

[Alexey M.]

Thanks for the explanation, it is interesting that the ":primary_key =>"
option does not have to point to the primary key!

[Toby Ovod-Everett]

I know this is an old thread, but as it seems to have the best advice
I've seen on this subject, I'm going to throw my few cents in here.

I'm strongly leaning in the direction of a shared primary/foreign key
for situations where the parent record is a prerequisite for the child
record. Effectively, the child record is an aspect of the parent
record. It could be modeled using a single parent table that is really
wide (i.e. that contains columns for all of the child records), but
there may be good reasons not to do that.

The approach I am taking is as follows:
Use the normal Rails :id column for both tables.

It appears in testing against Postgresql that one can have an
auto-increment primary key and still specify the primary key value when
creating a new record. In order to enforce this, I specify `validates
:id, presence: true, uniqueness: true` in the model for the child table.
If I run into a database that won't let me specify the value for an
auto-increment field when creating a new record I'll switch to using
`id: false` in the migration and manually create the `id` field as an
integer and then specify a unique index on it.

I specify belongs_to on the child record and pass `foreign_key: :id`. I
do the same for specifying has_one on the parent record.

There is no need to specify self.primary_key (the replacement for
set_primary_key) on the child model since the primary key is still :id.

I'm using a nested singular resource for the child record when routing
like so:

resources :user do
resource :instructor
end

That sets up routes like so:
/users/:id
/users/:user_id/instructor

Because I used :id throughout, when using the route helpers like
user_instructor_path, I can pass either a User or an Instructor object
and I will still get the correct route.

I'm still experimenting with this approach, but so far I'm reasonably
happy with it.

[Alexey Muranov]

My experimental application where i use shared primary/foreign key is
still in experimental phase. It mostly works, but i had to use
workarounds for saving/updating objects associated through shared
primary/foreign key (i do not want to look trough the details now). I
still think that a natural database structure (the one that looks
natural to me) is more important that Rails conventions.

[Matt Jones]

Sure, because you TOTALLY know more about database best practices than everybody else...

--Matt Jones