database design for sharing post from another user

Question

I'm developing a social network

my problem is how can i design sharing post in the database

what i have now

user table 

and

post table has a foreign key to user id

and

like table contain two foreign key user id vs post id

what i'm looking for is to have any small solution for sharing post from a user and connect this solution with like table because if some one share a post he will get new like on it

I dont know if creating a new share table is good but what about like now ?

any idea please ?

Answer 1

So, to sum up, you have:

• Users
• Posts
  • references Users
• Shares
  • references Users
  • references Posts
• Likes
  • references Users
  • references Posts or Shares

The SQL implementation of this domain model would be:

CREATE TABLE users (
    id int NOT NULL autoincrement
);
CREATE TABLE posts (
    id int NOT NULL autoincrement,
    user_id int NOT NULL,
    CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (id)
);
CREATE TABLE shares (
    id int NOT NULL autoincrement,
    user_id int NOT NULL,
    post_id int NOT NULL,
    CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (id),
    CONSTRAINT fk_posts FOREIGN KEY (post_id) REFERENCES posts (id)
);
CREATE TABLE likes (
    id int NOT NULL autoincrement,
    user_id int NOT NULL,
    post_id int,  /* NULLABLE */
    share_id int,  /* NULLABLE */
    CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (id),
    CONSTRAINT fk_posts FOREIGN KEY (post_id) REFERENCES posts (id),
    CONSTRAINT fk_shares FOREIGN KEY (share_id) REFERENCES shares (id)
);