Joining a pivot table to another table (HABTM join HABTM and model associations)

Let's say that I'm building an engine that has many blogs and many users. Users can have many blogs and blogs can have many users (users hasAndBelongsToMany blogs). I would create a pivot table called users_blogs which would look like this:

id user_id blog_id role

1  1       1       admin
2  1       2       poster
3  2       2       admin

So, user 1 belongs to blogs 1 and 2, and user 2 belongs to blog 2. User 1 is an admin in blog 1, and a poster in blog 2, and user 2 is an admin in blog 2.

There will also be posts. users_blogs will have many posts, and posts can have many users_blogs. They would be joined with a pivot table that would look like this:

id users_blog_id post_id
1  2             1
2  3             1

So, referring to users_blogs, user 1 on blog 2 collaborated with user 2 on blog 2 to create post开发者_运维问答 1.

I'm relatively new to MVC, so I'm wondering if A) the "role" data in the first table is where it's supposed to be?, and B) Is there a more correct way to do this structure? Frameworks will do the magic for me, I just want to make sure that this makes sense, and I've got the correct naming conventions. I'm using Cakephp, but I also think that this question could be answered by a Rails programmer.

Thank you!

It looks like you're on the right track. If I understand you, this is how it should be:

I didn't completely understand one of your statements, so I'm going on the assumption that a post belongsTo a single blog like most.

First issue is, your "pivot table" naming convention must be in alphabetical order - so it should be blogs_users, not users_blogs. Seems minor, but won't work otherwise.

Associations:

blog hasMany post
post belongsTo blog

user hasAndBelongsToMany blog //allowing more than one user to be associated to a blog
blog hasAndBelongsToMany user

user hasAndBelongsToMany post //allowing more than one user to be associated to a post
post hasAndBelongsToMany user

role hasAndBelongsToMany user //better to keep roles in a table that just a string
user hasAndBelongsToMany role //use 'with'=>'blogs_users' in your association
                              between user/roll

Tables:

users            (id, name, ...etc)
blogs            (id, title, ...etc)
roles            (id, role)
blogs_users      (id, user_id, role_id, blog_id)
posts            (id, title, blog_id)
posts_users      (id, post_id, user_id)

Update:

I'm not the best at this - still somewhat learning myself, but - off the top of my head, you could get the posts from blog 1 created by user 1 via something like this:

$blogId = '1';
$userId = '1';
$posts = $this->Post->find('all', array(
    'conditions' => array(
        'User.id' => $userId,
        'Blog.id' => $blogId
    )
);

This isn't really a rails, cakephp or mvc question but your relational database schema looks as it should based on what you described.

I don't know about Cakephp, but there are three problems about you join table which will break a habtm relationship in Rails.

1) The join table is named blogs_users, by default. 2) The join table must not have any other attributes, such as role. 3) The join table must not have an id.

So your blogs_users table should have only blog_id and user_id, otherwise you'll get errors.

If you want your join table to be a full fledged model in Rails, you'll want to use a has_many :through relationship. So

class User
  has_many :user_blogs
  has_many :blogs, :through => :user_blogs
end

class UserBlog
  belongs_to :blog
  belongs_to :user

  validates_presence_of :role, :in => ALLOWED_ROLE_NAMES # Example of logic in this model
end

class Blog
  has_many :user_blogs
  has_many :users, :through => :user_blogs
end