Optimal Database design regarding functionality of letting user share posts by other users
- by codecool
I want to implement functionality which let user share posts by other users similar to what Facebook and Google+ share button and twitter retweet.
There are 2 choices:
1) I create duplicate copy of the post and have a column which keeps track of the original post id and makes clear this is a shared post.
2) I have a separate table shared post where I save the post id which is a foreign key to post id in post table.
Talking in terms of programming basically I keep pointer to the original post in a separate table and when need to get post posted by user and also shared ones I do a left join on post and shared post table
Post(post_id(PK), post_content, posted_by)
SharedPost(post_id(FK to Post.post_id), sharing_user, sharedfrom(in case someone shares from non owners profile))
I am in favour of second choice but wanted to know the advice of experts out there?
One thing more posts on my webapp will be more on the lines of facebook size not tweet size.
