How to implement the like and share feature for a social networking website? [closed]

I want to understand how the like button is actually implemented in a Web App (the database table part).

How to create a table structure for a like and share button?

Post can have share count and like count.

What about who shared and who liked entries?

Is this possible with a relational database or do I need another database type?

Answer

You would typically create two bridge tables to represent the like and share relationships between users and posts.

Sample ddl:

create table posts (
    post_id int primary key,
    -- more columns here: post date, title, content...
);

create table users (
    user_id int primary key,
    -- more columns here: user name, ...
);

create table user_likes_post (
    post_id int,
    user_id int,
    -- more columns here: date when the user liked the post, ...
    primary key(post_id, user_id)
);

create table user_shares_post (
    post_id int,
    user_id int,
    -- more columns here: date when the user shared the post, ...
    primary key(post_id, user_id)
);

Every time a users likes a post, a new record is created in the user_likes_post table; sames goes when a user shares a post with table user_shares_post.

Now, say that you want to know how many users liked each post, you can do:

select
    p.*,
    (select count(*) from user_likes_post ulp where ulp.post_id = p.post_id) no_likes   
from posts p