PostgreSQL / express rookie - create new table or just a new column in existing table?

Question

This will probably get closed for being opinion based but hopefully I can get a comment or two.

I'm building a social media app (Express/Postgres/React) with posting, friending, etc. I found some starter Postgres code that creates tables for users, posts & followers with associated constraints and it works great.

As I expand it, what I'm struggling with, broadly speaking, is knowing when to create a new table for something and when to just store that data in an existing table, in a new column. For example, "Likes" — if a person Likes a post, do I add a "Like" column to the post table and store user_ids there? Or, do I create a dedicated "Likes" table and store user ids & post ids.

Both ways work in some cases but I fear I'll choose one, flesh it out and realize I need to go the other way or be stuck with a less performant method.

Answer 1

You have a many-to-many relationship between users and posts. A user may like multiple posts, and posts may receive may likes. To represent that relationship, you would typically create a bridge table, with foreign keys that reference the users and posts table.

So:

create table likes (
    user_id int references users(user_id),
    post_id int references posts(post_id),
    primary key (user_id, post_id)
);

You may want to add more columns to the table, such as the date when the post was liked for example.