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.
在我正在创建的一个社交网站上，我需要一些方法来存储用户“喜欢”的帖子，以确保他们每次只能“喜欢”一个帖子。我有几个想法。
- A seperate table for each user, to store all of the different posts' IDs which they've liked as rows in said table.
- 为每个用户提供一个独立的表，以存储他们喜欢的所有不同的post id作为该表中的行。
-
A space-seperated string of post IDs as a field in the table
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.
每个post都有一个独立的表，它存储所有不同用户的id，这些id都喜欢将这个post作为表中的行。
note, users is a table containing all of the site's users, with their ID, username, etc.
注意，users是一个包含站点所有用户的表，包括他们的ID、用户名等。
-
- - - - - -
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.
所以我想为每一行space-seperated字符串在用户可能是一个好主意,因为我不需要开始使用更多的表(这可能会使事情复杂化,)但是我觉得使用space-seperated字符串会降低性能远远超过使用额外的表,尤其是在更大数量的用户。
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?
本质上，我的问题是:在上面提到的确保用户只喜欢一篇文章的方法中，哪一种最实用?
1 个解决方案
#1
2
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.
通常情况下，必须动态创建表，无论是针对用户还是针对帖子，都不是一个好主意。它不仅会使SQL生成变得复杂，而且还会使数据字典中充满对象，使数据库的维护变得更加复杂。
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.
逗号分隔的字符串也不是一个好主意。它破坏了1NF将使您的查询变得复杂(或者更糟——使您的代码正确!)
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:
明智的方法是使用一个表来关联用户和文章。每一行都包含一个用户ID和一个他喜欢的帖子的ID，在这两行之上创建一个复合主键将确保用户不会喜欢一个帖子两次:
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)
);
#1
2
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.
通常情况下，必须动态创建表，无论是针对用户还是针对帖子，都不是一个好主意。它不仅会使SQL生成变得复杂，而且还会使数据字典中充满对象，使数据库的维护变得更加复杂。
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.
逗号分隔的字符串也不是一个好主意。它破坏了1NF将使您的查询变得复杂(或者更糟——使您的代码正确!)
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:
明智的方法是使用一个表来关联用户和文章。每一行都包含一个用户ID和一个他喜欢的帖子的ID，在这两行之上创建一个复合主键将确保用户不会喜欢一个帖子两次:
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)
);