CODEWITHSUNDEEP
ruby-on-railsdatabase-designdata-modeling
Jim Jones
Jim Jones

Reputation: 2837

Modeling polymorphic blog posts

Say I have a blog_posts table. But, the users writing posts belong to groups, and each group has its own blog.

For example:

Say, a user belongs to 3 groups: Marketing, Project Alpha, Administrators

He creates a blog post, but want that post to appear in the "Marketing Blog" and on the "Project Alpha" blog.

What would be the best way to model this?

Would it be a bad idea to have a field in the blog_posts table like: group_ids

and store a comma-delimited list of ids: 3,7 (where Marketing Group =3, and Project Alpha=7)

or should I create another table and store blog_posts id and groups?

Thanks.

Upvotes: 0

Views: 125

Answers (3)

Chris Bailey
Chris Bailey

Reputation: 4136

Building on @minitech's answer i'd have 3 model's and 5 db tables

Models rails g model <model_name>

  • Post (must have column user_id for FK)
  • User
  • Group

Additional tables rails g migration <table_name>

  • groups_posts (columns group_id and post_id only)
  • groups_users (columns group_id and user_id only)

Then in your models configure the relations as follows

class Post < ActiveRecord::Base
  has_and_belongs_to_many :groups
  belongs_to :user
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :groups
  has_many :posts
end

class Group < ActiveRecord::Base
  has_and_belongs_to_many :users
  has_and_belongs_to_many :posts
end

Then as per your example the code becomes:

user = User.find(<id of user>)
post = Post.create(:title => 'foo', :content => 'bar', :user => user)
post.groups << Group.find(3)
post.groups << Group.find(7)

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

Using comma delimited lists as a way around making all data items atomic is a bad idea. It's basically a way of taking data that is not in first normal form and transfomring so that it looks like first normal form data even though it's not.

If you do this, for certain queries you will have to do a full scan of one of the tables instead of an indexed lookup. This will be monstrously slow, and get worse as data volume increases.

A better solution is to have a junction table that relates group_id to user_id. 

 Create Table Group_user 
(group_id,
 user_id primary key is (gorup_id,  user_id))

Then do a three way join when you want all the data together. This is the basic way you model a many-to-many relationship.

Upvotes: 0

Ry-
Ry-

Reputation: 225164

I would recommend creating one table to hold all posts for all blogs, then have a table to store all the blogs' information, then a table to store all the blogs' posts, referenced by ID. Then you just add, for example:

Into posts: ID=(auto increment value), Title='The title', Content='The content'
Into blog posts: blogID=(the ID of blog #1 to insert the post into), postID=(the auto increment value from last time)
Into blog posts: blogID=(the ID of blog #2 to insert the post into), postID=(the auto increment value from last time)

That way, the post is linked in so when it is edited or removed changes are reflected on all blogs, and you can also get a quick list of all blogs a post is in using something like SELECT * FROM blogPosts WHERE postID=id_of_post.

Upvotes: 0

Related Questions