Recommended Table Set up for one to many/many to one situation

Question

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.

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:

User   VisiblePosts

user1 posting1, 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?

Answer 1

Three tables...

User: [UserId] [OtherField]

Post: [PostId] [OtherFields]

UserPost: [UserId] [PostId]

User.UserId Joins to UserPost.UserId, Post.PostId Joins to UserPost.PostId

Then look up the table UserPost, joining to Post when you are selecting which posts to show

Answer 2

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.

• PostingTable with PostingID (and other data)

• PostingVisibilityTable with PostingID and UserID

• UserTable with UserID and user data

Create the postings independently of their visibility rights, and then separately add/remove PostingID/UserID pairs against the Visibility table.

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"

Answer 3

This is a many-to-many relationship or n:m relationship.

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.