Do I really need to have an ID to a child table in rails using active records ?

Question

I have a user model (sql table) that has_one : profile. profiles have user_id to refer to an user object. Do i really need to have profile_id column ?

What are the advantages and disadvantages ? I guess one advantage will be the size of the table that will be 1 collumn smaller. What are the disadvantages ?

Answer 1

With the profile's belongs_to :user association, if you want the association to be available the next time the object is loaded from the database, you'd better store the user_id in the profiles table. There's really no other way. However, you certainly don't need profile_id column for the opposite association. Rails is smart enough to look in the profiles table when interpreting the User's has_one :profile association.

That said, in general the cost of a single integer database column is nothing to worry about. In this case, you don't need the integer column, but consider that the user's email address will be several times larger in terms of bytes of storage and index size.

There are some disadvantages to having the profile_id column, by the way. Rails, to my knowledge, wouldn't even update the column automatically if you reassign the profile attribute on the a User. That behavior is particular to a belongs_to association.

Moreover, storing redundant information in a relational database is asking for trouble, since it opens up possibility of a contraction. If user 1 points to profile 1, but profile 1 points to user 2, how do you interpret that? Of two redundant facts, which one do you trust? And if one fact is always trusted over another, what's the point of storing the second fact at all? This is sort of tangential to your question, but I thought it was worth mentioning nonetheless.

Answer 2

Your profiles table does not need a profile_id column. It already has id, and it belongs_to User, so all you need is user_id in your profiles table. The foreign key (the id of the object that Profile belongs to) follows belongs_to.

Profile has_one :user
User belongs_to :profile
# User table includes its own id ('id') and profile_id for the belongs_to

User has_one :profile
Profile belongs_to :user
# Profile table includes its own id ('id) as well as user_id for the belongs_to

Answer 3

No. You don't need id. But you need primary key, which may be other column in table. Naturally primary key means that value must be unique and not null.

Just create table without id column:

create_table "profiles", :id => false, primary_key => 'user_id' do |t|
  t.integer "user_id"
  #...
end

Then in model:

class Profile < ActiveRecord::Base
  set_primary_key 'user_id'
end

The table shouldn't have autoincrement or sequencer created - just check that. The value should be set by you or by framework if you are using association method (user.profile.create(...)) .

Disadvantages: Some plugins may expect 'id' to be PK but this means they are poorly written.

Answer 4

I see no disadvantages, assuming it is really a duplicated column and is not a legacy code that would break some existing code.

Another advantage is that Rails is guided by conventions. One of them is that primary keys are a column named id, since many other methods rely on it. If you break the conventions, you'll have to adapt other part of your code, and avoid the advantages that come automatically with Rails (they say, they'll come to bite you)