I need to create a script where someone will post an opening for a position, and anyone who is eligible will see the opening but anyone who is not (or opts out) will not see the opening. So two people could go to the same page and see different content, some potentially the same, some totally unique. I'm not sure the best way to arrange that data in a MySQL DB/table.
我需要创建一个脚本,其中有人会发布一个职位的开头,任何有资格的人都会看到开场,但任何不(或选择退出)的人都不会看到开场。因此,两个人可以访问同一页面,看到不同的内容,一些可能相同,一些完全独特。我不确定在MySQL DB /表中安排数据的最佳方法。
For instance, I could have it arranged by the posting, but that would look sort of like:
例如,我可以通过发布来安排它,但这看起来有点像:
PostID VisibleTo
PostingA user1,user2
And that seems wrong (the CSV style in the column). Or I could go with by person:
这似乎是错误的(列中的CSV样式)。或者我可以和人一起去:
User VisiblePosts
user1 posting1, posting2
user1 posts1,posting2
But it's the same problem. Is there a way to make the user's unique, the posting unique, and have them join only where they match?
但这是同样的问题。有没有办法让用户的独特性,发布独特,并让他们只在他们匹配的地方加入?
The decision is initially made by doing a series of queries to another set of tables, but once that is run, it seems inefficient to have that some chunk of code run again and again when it won't change after the user posts the position.
这个决定最初是通过对另一组表进行一系列查询来做出的,但是一旦运行,在用户发布位置后不会发生变化时,一些代码块会反复运行似乎效率低下。
...On second thought, it MIGHT change, but if we assume it doesn't (as it is unlikely, and as little consequence if a user sees something that they are no longer eligible for), is there a standard solution for this scenario?
......第二个想法,它可能会发生变化,但如果我们认为它没有(因为它不太可能,并且如果用户看到他们不再符合条件的东西那么重要),是否有一个标准的解决方案场景?
3 个解决方案
#1
This is a many-to-many relationship or n:m relationship.
这是多对多关系或n:m关系。
You would create an additional table, say PostVisibility, with a column PostID and UserID. If a combination of PostID and UserID is present in the table, that post is visible to that user.
您将创建一个附加表,例如PostVisibility,其中包含PostID和UserID列。如果表中存在PostID和UserID的组合,则该帖子对该用户可见。
#2
Three tables...
User: [UserId] [OtherField]
用户:[UserId] [OtherField]
Post: [PostId] [OtherFields]
发布:[PostId] [OtherFields]
UserPost: [UserId] [PostId]
UserPost:[UserId] [PostId]
User.UserId Joins to UserPost.UserId, Post.PostId Joins to UserPost.PostId
User.UserId加入UserPost.UserId,Post.PostId加入UserPost.PostId
Then look up the table UserPost, joining to Post when you are selecting which posts to show
然后查找表UserPost,在选择要显示的帖子时加入Post
#3
Edit: Sorry, I think you are speaking in Posting-User terms, which is many-to-many. I was thinking of this in terms of posting-"viewing rights" terms, which is one-to-many.
编辑:对不起,我想你是在发帖 - 用户条款,这是多对多的。我在考虑发布“观看权利”条款时考虑到了这一点,这是一对多的。
Unless I am missing something, this is a one-to-many situation, which requires two tables. E.g., each posting has n users who can view it. Postings are unique to an individual user, so you don't need to do the reverse.
除非我遗漏了某些东西,否则这是一对多的情况,需要两张桌子。例如,每个帖子都有n个用户可以查看它。过帐对于单个用户是唯一的,因此您无需执行相反的操作。
-
PostingTable with PostingID (and other data)
PostingTable with PostingID(和其他数据)
-
PostingVisibilityTable with PostingID and UserID
使用PostingID和UserID发布PostionVisibilityTable
-
UserTable with UserID and user data
具有UserID和用户数据的UserTable
Create the postings independently of their visibility rights, and then separately add/remove PostingID/UserID pairs against the Visibility table.
独立于其可见性权限创建发布,然后针对可见性表单独添加/删除PostingID / UserID对。
To select all postings visible to the current user:
要选择当前用户可见的所有发布:
SELECT * FROM PostingTable A INNER JOIN PostingVisibilityTable B ON A.PostingID = B.PostingID WHERE B.UserID = "currentUserID"
#1
This is a many-to-many relationship or n:m relationship.
这是多对多关系或n:m关系。
You would create an additional table, say PostVisibility, with a column PostID and UserID. If a combination of PostID and UserID is present in the table, that post is visible to that user.
您将创建一个附加表,例如PostVisibility,其中包含PostID和UserID列。如果表中存在PostID和UserID的组合,则该帖子对该用户可见。
#2
Three tables...
User: [UserId] [OtherField]
用户:[UserId] [OtherField]
Post: [PostId] [OtherFields]
发布:[PostId] [OtherFields]
UserPost: [UserId] [PostId]
UserPost:[UserId] [PostId]
User.UserId Joins to UserPost.UserId, Post.PostId Joins to UserPost.PostId
User.UserId加入UserPost.UserId,Post.PostId加入UserPost.PostId
Then look up the table UserPost, joining to Post when you are selecting which posts to show
然后查找表UserPost,在选择要显示的帖子时加入Post
#3
Edit: Sorry, I think you are speaking in Posting-User terms, which is many-to-many. I was thinking of this in terms of posting-"viewing rights" terms, which is one-to-many.
编辑:对不起,我想你是在发帖 - 用户条款,这是多对多的。我在考虑发布“观看权利”条款时考虑到了这一点,这是一对多的。
Unless I am missing something, this is a one-to-many situation, which requires two tables. E.g., each posting has n users who can view it. Postings are unique to an individual user, so you don't need to do the reverse.
除非我遗漏了某些东西,否则这是一对多的情况,需要两张桌子。例如,每个帖子都有n个用户可以查看它。过帐对于单个用户是唯一的,因此您无需执行相反的操作。
-
PostingTable with PostingID (and other data)
PostingTable with PostingID(和其他数据)
-
PostingVisibilityTable with PostingID and UserID
使用PostingID和UserID发布PostionVisibilityTable
-
UserTable with UserID and user data
具有UserID和用户数据的UserTable
Create the postings independently of their visibility rights, and then separately add/remove PostingID/UserID pairs against the Visibility table.
独立于其可见性权限创建发布,然后针对可见性表单独添加/删除PostingID / UserID对。
To select all postings visible to the current user:
要选择当前用户可见的所有发布:
SELECT * FROM PostingTable A INNER JOIN PostingVisibilityTable B ON A.PostingID = B.PostingID WHERE B.UserID = "currentUserID"