CODEWITHSUNDEEP
mysqlmany-to-manyone-to-many
chris
chris

Reputation: 36947

mySQL table design for facebook style posts that suits one-to-many or many-to-many

I am trying to build / design a table that will serve purpose to a post/comment system similar to google+ that can support bidirectional one-to-many/many-to-many relations. What I have currently is

member_id, 
post_id, 
post_body, 
post_date, 
post_flagged, 
post_shared, 
post_likes

I have a comment table thats pretty much the same as the above except it has a comment_id on top of the ones above listed to server as its auto incriminate column.

What I am ultimately seeking here currently is I guess a sanity check. Does the above construct/schema look like it might work? would you add/remove/alter it in any way? If not/or if so. Can anyone also suggest what might be a good way to do the post_shared column? Is there some type of object or means of storing data in that column that would serve purpose to the logic of Google+ Circles?

Upvotes: 0

Views: 391

Answers (2)

Logan Serman
Logan Serman

Reputation: 29880

From what I understand, your post_shared column is what is really in question here. Let's say you have a post that is directed at 5 "circles" (we will steal this term from Google). How would you indicate that in your current setup?

The only way to do this in one field is something like {1, 2, 3, 4, 5} where those numbers are the circle id's. This isn't a very good practice.

The best way I can see to accomplish this would be to have this schema:

  • Posts: post_id, post_body, post_flagged, post_likes
  • Circles: circle_id, ...
  • PostCircles: post_id, circle_id

The PostCircles table will show which posts are visible to which circles. In the example above, you have 5 rows in the PostCircles table. Let's say the post id is 1:

post_id    |    circle_id
1               1
1               2
1               3
1               4
1               5

Then you can use your query language to show display the appropriate posts, depending on which circles the viewer is in (note the user schema is not shown in this answer).

Upvotes: 1

Randy
Randy

Reputation: 16677

post_shared should not be in the main post table.

for a normalized solution, you would want a circles table, a users table, a posts table and some associative tables to tell which ones are related - something like

user_circles
post_circles

Upvotes: 1

Related Questions