0

If a person has many posts, we can either have a People table and a Posts table. Each entry in Posts table will have a key pointing back to an entry in People. A typical has_many relationship.

Or we can have a People and a Posts table, as well as a relation table that has both keys from entries in People and those in Posts to connect them.

It seems both are feasible so how do I decide which one to choose over another?

OneZero
  • 11,556
  • 15
  • 55
  • 92

6 Answers6

2

The two solutions you exposed solve actually two different problems :

  • The first one is a one to many relationships (more info in the guide). Basically, a post is tied to a unique post, so there's no need for an extra table.

  • If somehow a post can be related to more than one unique person, then you have a many to many relationship (more info in the guide). In that case, the join tabe between posts and people will allow you to know which people are tied to a post, and which posts are tied to a user. Note that you could also use a has_many :through (more info in the guide), but this is another discussion.

pjam
  • 6,356
  • 5
  • 30
  • 40
1

You use the third table (a join table) when you have a 'many to many' relationship. That is, a person can have many posts, and a post can have many people.

In your example, if a post can only belong to one person, it's a 'one to many' and you don't need and shouldn't create a join table. The foreign key in the posts table is ensuring that a post belongs only to one person. With a join table you break that constraint.

boulder
  • 3,256
  • 15
  • 21
0

The difference is these are 2 different relations.

  • The "has_many" (without connecting table) implies an person has many posts, and a post belongs to a UNIQUE person.
  • The "has_and_belongs_to_many" (with this connecting table) implies that a person has many posts, and a post have many persons.

Difference Between One-to-Many, Many-to-One and Many-to-Many?

Community
  • 1
  • 1
pierallard
  • 3,326
  • 3
  • 21
  • 48
0

You method 2(many to many) is only necessary if a Post will have multiple authors, which is rare case.

In most case your method one is good enough.

Billy Chan
  • 24,625
  • 4
  • 52
  • 68
0

If you have a join table, that usually means you have a many-to-many relationship between the two models. In your example, it means that a post can belong to many users and a user can belong to many posts.

If you're not using a join table, that means you have a one-to-many or one-to-one relationship between the two models. Look at the following case below:

Case 1: user_id in posts

This means that posts belong_to a user. And a user can have either a has_many or has_one relationship with posts

Case 2: post_id in users

Obviously this is just the opposite of case 1. But I have to emphasize that where you place the foreign key is essential to your database model.

jvnill
  • 29,479
  • 4
  • 83
  • 86
0

You use a "One to Many" relation ship (one table has a foreign key to another table) when the associated entries can not be shared amongst several owners. In you example : if a Post only belongs to one user, you will use a this kind of relation ship (the Posts table will contains the foreign key to People table).

You will use a relation table to represents a "Many To Many" relationship. Still using you exemple, if a People can have several Posts, and a Post can belongs to more than one people (imagine that a post can be created by more than one people), you will need a third table to store those relations. This relation table will allow you to have several People ID associated to the same Post ID, or several Post ID associated to the same people.

Regards

Marc
  • 116
  • 2
  • 3