CODEWITHSUNDEEP
ruby-on-railsdatabaseschema
po.studio
po.studio

Reputation: 4127

Database modeling this relationship?

Rails app:

  1. A user has_many positions.

  2. Each position has one company (company name and company id) per the following schema:

    create_table "positions", :force => true do |t|
  t.integer  "user_id"
  ...
  t.string   "company"
  t.integer  "company_id"
end
  3. I would like users to be able to "follow" as many individual companies as they would like (i.e. a user can follow many different companies a company can be followed by many different users). It would seem that this calls for a has_and_belongs_to_many relationship between users and positions, but I want users to be able to follow the company attribute of a position row and not the position itself.

Should I create a new "following" table altogether that would pull companies from the positions table to be matched to user_id's? Or is there a way I can set up a has_many :through relationship and map user_id's to company_id's?

Thank you!

Upvotes: 1

Views: 92

Answers (2)

Chris Lewis
Chris Lewis

Reputation: 1325

I broadly agree with MickJ, although having created the Company and User models/tables (which obviously have an id column in each) I'd do it as: 

create_table "companies" do |t|
   t.string "name"
   ... 
end

create_table "positions" do |t|
   t.references "user"
   t.references "company"
   ...
end

create_table "followings" do |t|
   t.references "user"
   t.references "company"
   ...
end

Models: 

class User
  has_many :positions
  has_many :followings
end 

class Company
  has_many :positions
  has_many :followings
end 

class Position
  belongs_to :user
  belongs_to :company
end 

class Following
  belongs_to :user
  belongs_to :company
end

You could reference the company from the position by doing: 

position = Position.first
puts position.company.name

or by user with something like

user = User.first
user.positions.each do |position| 
  puts position.company.name
end

-- EDIT1:

To extract the company name from positions into a separate table you'd be best off writing a little rake task - something like: 

Position.all.each do |position|
  company = Company.find_or_initialize_by_name(position.company_name)
  position.company_id = company.id
  position.save
end

Then you might want to write a migration to remove the company name column from the positions table ... just to keep things tidy.

Upvotes: 0

MickJ
MickJ

Reputation: 2197

What I think you could have

A User table:

integer User_Id
....

A Company Table:

string company
integer company_id
...

A Positions table:

integer user_id foreign_key -> User table
integer company_id foreign_key -> company table

A Following table (If the user can follow any comapny regarding of whether he has a position in it):

integer user_id foreign_key -> User table
integer company_id foreign_key -> company table

OR if the user can only follow a company that he has position in then you can add a new column to position table. This would be a boolean flag telling if the user if 'following' the company identified by the position. Alternatively the Following table can also map user to position in this case.

Upvotes: 1

Related Questions