CODEWITHSUNDEEP
mysqldatabasedatabase-designinnodbsocial-networking
Jacob Garby
Jacob Garby

Reputation: 855

A seperate table for the posts which each user has liked - practical or not?

In a social networking site I'm making, I need some way to store which posts a user has 'liked', to ensure they can only 'like' each post one time. I have several ideas.

note, users is a table containing all of the site's users, with their ID, username, etc.

-

Initially I liked the idea of a seperate table for each user, but I realised this might be more trouble than it's worth.

So I thought a space-seperated string for each row in users might be a good idea, since I wouldn't have to start working with many more tables (which could complicate things,) but I have a feeling using space-seperated strings would lower performance significantly more than using additional tables, especially with a greater amount of users.

Essentially my question is this: Which, out of the aforementioned methods of making sure a user can only like a post once, is the most practical?

Upvotes: 2

Views: 372

Answers (1)

Mureinik
Mureinik

Reputation: 311403

None of these sound like particularly good ideas.

Generally, having to create tables on the fly, be it for users or posts, is a bad idea. It will complicate not only your SQL generation, but also clutter up the data dictionary with loads of objects and make maintaining the database much more complicated than it should be.

A comma-delimited string also isn't a good idea. It breaks 1NF will complicate your queries (or worse - make you right code!) to maintain it.

The sane approach is to use a single table to correlate between users and posts. Each row will hold a user ID and the ID of a post he liked, and creating a composite primary key over the two will ensure that a user can't like a post twice:

CREATE TABLE user_post_likes (
    user_id INT, -- Or whatever you're using in the users tables
    post_id INT, -- Or whatever you're using in the posts tables
    PRIMARY KEY (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (post_id) REFERENCES post(id)
);

Upvotes: 3

Related Questions