What are some potential pitfalls of storing likes, shares, and comments as separate entries in a relational database for a social network platform like Facebook?

One potential pitfall of storing likes, shares, and comments as separate entries in a relational database is that it can lead to redundant data and inefficient queries when trying to retrieve all interactions for a specific post. To solve this issue, a better approach would be to create a single "interactions" table that stores all types of interactions (likes, shares, comments) with a column indicating the type of interaction. 

// SQL query to create an interactions table
CREATE TABLE interactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    user_id INT,
    interaction_type ENUM('like', 'share', 'comment'),
    created_at TIMESTAMP
);

// SQL query to insert a new interaction
INSERT INTO interactions (post_id, user_id, interaction_type, created_at) 
VALUES (1, 2, 'like', NOW());

// SQL query to retrieve all interactions for a specific post
SELECT * FROM interactions WHERE post_id = 1;

Keywords

relational database data redundancy normalization performance scalability

Related Questions