ruby on rails - Advice on RoR database schema and associations

Question

New to RoR, but am having a stab at building an app - and I'm looking for some advice on my db structure.

I've got 4 models/tables: Users > Clients > Jobs > Tasks

The app will work as follows:

1. Users will login
2. They can add Clients
3. They can add Jobs to Clients
4. They can add Tasks to Jobs

So, Tasks belong to Jobs, Jobs belong to Clients, and Clients belong to Users.

I want to query the DB for any one of a Client, a Job, or a Task and also make sure that it belongs to the currently logged-in User. I'm struggling to write a 'railsy' join query and design my associations so I can do this.

I know this would be super easy if I had a user_id field in every table, but that doesn't seem like the right way to do it.

I've read the guide at http://guides.rubyonrails.org/association_basics.html, but am still in the dark a little. Can anyone shed some light on how I might structure my DB - and more importantly my associations?

Thx.

Answer 1

It seems you have your associations set up right from one side, all you have to do is add the other end of the associations using has_many:

class Task < ActiveRecord::Base
  belongs_to :job
end

class Job < ActiveRecord::Base
  belongs_to :client
  has_many :tasks
end

class Client < ActiveRecord::Base
  belongs_to :user
  has_many :jobs
  has_many :tasks, :through => :jobs
end

class User < ActiveRecord::Base
  has_many :clients
  has_many :jobs, :through => :clients
  has_many :tasks, :through => :jobs
end

Now ActiveRecord will take care of the joins for you. It's true that in a pure db schema, you should not have the user_id in more than one place(here that would the clients table). However sometimes it would be added also to the tasks and jobs table for a performance boost, because then the db queries would not be so big. Nevertheless you have to put more effort into making your data consistent - you have to ensure that a job has the same user_id as its client for example.

Then you would be able to define shortcut associations like:

class Task < ActiveRecord::Base
  belongs_to :user
end

But in this case I would not do it unless you notice the queries are too slow for your needs. Premature optimization is evil :-)