I’m currently creating a social media platform similar to Reddit. I want my users to be able to scroll through their home feed day after day and never see the same post more than once, just like on Reddit or Twitter. What is the best method to approach this?

I was thinking about creating a table which stores the post view history of every user, and whenever a user fetches posts, I would make sure none of the posts being returned to the user were in the view history for that specific user. However, I have 2 main concerns.

1. Storing the post-view history for every user and for each post they view would lead to a very large number of entries.

2. Everytime any user wants to fetch posts on their home feed, it would be comparing each of those posts against the very large table to make sure the user hasn’t already the post.

Here is my database structure so far:

--@block
CREATE TABLE `users` (
    `uid` varchar(255) NOT NULL,
    `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `quote` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`uid`),
    UNIQUE KEY `username` (`username`),
    CONSTRAINT `username_format` CHECK (
        regexp_like(`username`, _utf8mb4 '^[A-Za-z0-9_]+$')
    )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

--@block
CREATE TABLE `posts` (
    `id` int NOT NULL AUTO_INCREMENT,
    `user_id` varchar(255) NOT NULL,
    `title` varchar(255) NOT NULL,
    `body` text NOT NULL,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_posts_users` (`user_id`),
    CONSTRAINT `fk_posts_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`uid`) ON DELETE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

--@block
CREATE TABLE `comments` (
    `id` int NOT NULL AUTO_INCREMENT,
    `user_id` varchar(255) DEFAULT NULL,
    `post_id` int NOT NULL,
    `parent_comment_id` int DEFAULT NULL,
    `content` text,
    `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_comments_users` (`user_id`),
    KEY `fk_comments_posts` (`post_id`),
    KEY `fk_comments_comments` (`parent_comment_id`),
    CONSTRAINT `fk_comments_comments` FOREIGN KEY (`parent_comment_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_comments_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_comments_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`uid`) ON DELETE
    SET NULL
) ENGINE = InnoDB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

--@block
CREATE TABLE `tags` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

--@block
CREATE TABLE `post_tags` (
    `post_id` int NOT NULL,
    `tag_id` int NOT NULL,
    PRIMARY KEY (`post_id`, `tag_id`),
    KEY `fk_post_tags_tags` (`tag_id`),
    CONSTRAINT `fk_post_tags_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_post_tags_tags` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

--@block
CREATE TABLE `post_votes` (
    `post_id` int NOT NULL,
    `user_id` varchar(255) NOT NULL,
    `vote_type` enum('upvote', 'downvote') NOT NULL,
    PRIMARY KEY (`post_id`, `user_id`),
    KEY `fk_post_votes_users` (`user_id`),
    CONSTRAINT `fk_post_votes_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_post_votes_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`uid`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

--@block
CREATE TABLE `comment_votes` (
    `comment_id` int NOT NULL,
    `user_id` varchar(255) NOT NULL,
    `vote_type` enum('upvote', 'downvote') NOT NULL,
    PRIMARY KEY (`comment_id`, `user_id`),
    KEY `fk_comment_votes_users` (`user_id`),
    CONSTRAINT `fk_comment_votes_comments` FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_comment_votes_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`uid`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

CREATE TABLE `post_views` (
    `post_id` int NOT NULL,
    `user_id` varchar(255) NOT NULL,
    PRIMARY KEY (`post_id`, `user_id`),
    KEY `fk_post_views_users` (`user_id`),
    CONSTRAINT `fk_post_views_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_post_views_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`uid`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;

Offtopic, but, when I display any posts or comments, I will need to also display the username of the author of that post or comment. I am considering making the username column in the users table the primary key as it is unique and the user cannot change it after creation. This would allow me to have the username property already whenever I fetch any post or comment, instead of running a query for each post or comment being displayed to fetch the username using the uid. However, I initially chose to make the uid the primary key since it hold the user’s firebase id, which is guaranteed to be unique and will always reference the same object. I do not know which route would be ideal.

Thank you for your time 🙂