0

I am creating an app where users can create content, other users can like, comment those posts, now I want to implement sharing feature. I want to know what would the best way to implement this. Following is the table structure for posts

| post_id | post_content | post_user | post_date |

Users can see post only from people they are following.

First of all I thought I would add another row to the above posts table but that would create unnecessary duplication of data, then I thought of adding rows to my user_action table in which I am storing post likes and comments, but thats making the system complicated..

I searched over SO, but couldn't find anything, or may be my search terms were not reachable to those questions.

Is there any better way for achieving this?

vikas devde
  • 11,691
  • 10
  • 35
  • 42
  • can you post the complete structure of the tables that you've already created ? – Maximus2012 Jul 20 '13 at 15:12
  • you have not created any table as of now ? – Maximus2012 Jul 20 '13 at 15:13
  • you can see my posts table above – vikas devde Jul 20 '13 at 15:14
  • ok... looks like there are not many tables to start with. In that case, I would say start with the post table that you have, write some sample php code that makes use of that table, and as your application progresses, add more tables as and when needed. The basic idea is that your db table design should be dependent on your application needs (and not the other way around). – Maximus2012 Jul 20 '13 at 15:16
  • Additionally you might also want to look into the DB design of wordpress since it looks like you are trying to do something similar here. – Maximus2012 Jul 20 '13 at 15:17

1 Answers1

0

I agree that adding another row with duplicated data is not good. Let's take a closer look at what you're trying to do.

You have users, and you have posts. These should definitely be separate entities with their own table.

'Like' is a N-M relationship since a user can like any number of posts, and a post can be liked by any number of users. So this will be its own table with two foreign keys, user and post.

Comments are similar to posts, but contains some contents. The same user and post combination can also contain multiple comments. Comments will also be its own table with two foreign keys, user and post. The combination of these two will be unique in like, but non-unique in comment.

Now the sharing part. You want users to be able to follow other users. This is also an N-M relationship since any user can follow any number of users, and any user can be followed by any number of users. Thus, you'll want one more table for this relationship with two foreign keys, user and user. The combination of these two user keys will be unique.

For a given user John, to find all posts of users that he's following, we can use the following pseudo query:

select posts.*
from posts
join user intermediateUser on posts.author = intermediateUser.id
join follow on follow.followee = intermediateUser.id
where follow.follower = John
John Tseng
  • 6,262
  • 2
  • 27
  • 35