每个用户喜欢的帖子的单独表格-是否有用? [英] 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.

• 每个用户的单独表格，用于将他们喜欢的所有不同帖子的ID存储为所述表格中的行.

• 由空格分隔的一串帖子ID，作为表users中的字段.

每个 post 的单独表，将喜欢该帖子的所有不同用户的ID存储为表中的行.

A seperate table for each post, storing all of the different users' IDs which have liked said post as rows in the table.

请注意，users是一个包含网站所有用户及其ID，用户名等的表.

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.

所以我认为users中每行的空格分隔的字符串可能是一个好主意，因为我不必开始使用更多的表(这可能会使事情变得复杂)，但是我有一种使用的感觉与空格分开的字符串相比，使用其他表将大大降低性能，尤其是在使用大量用户的情况下.

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.

基本上我的问题是:在上述确保用户只喜欢一次帖子的方法中，哪一种是最实用的?

推荐答案

这些听起来都不是特别好的主意.

None of these sound like particularly good ideas.

通常，无论是为用户还是为帖子发布，都必须即时创建表是一个坏主意.这不仅会使您的SQL生成变得复杂，而且会使数据字典中的对象杂乱无章，并使数据库的维护比原本应该的复杂得多.

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.

用逗号分隔的字符串也不是一个好主意.它破坏了 1NF 会使查询复杂化(或更糟糕的是，使您的代码正确！)来维护它.

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.

理智的方法是使用单个表在用户和帖子之间建立关联.每行将包含一个用户ID和他喜欢的帖子的ID，并且在这两行上创建一个复合主键将确保用户不会两次喜欢该帖子:

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)
);

这篇关于每个用户喜欢的帖子的单独表格-是否有用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！