Optimal Database design regarding functionality of letting user share posts by other users

Posted by codecool on Programmers See other posts from Programmers or by codecool
Published on 2012-03-26T12:51:23Z Indexed on 2012/03/26 17:39 UTC
Read the original article Hit count: 248

Filed under:
|

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.

© Programmers or respective owner

Related posts about database-design

Related posts about postgres