Table of Users having many to many relationship with itself? 1

[jpo245]

Hi everyone,
i hope to implement a user subscription feature on my webdatabase, where users can subscribe to each other's postings.

Can i achieve this by having one table to map the USERIDs to each other, and then relate this to the main USER table?

Currently I have two user tables that are exact mirrors of each other, and then a junction table to relate the two, but im beginning to think this is kinda dumb....

Thanks!

 

[TonyGroves]

You're certainly on the right track there. Having two user tables is asking for trouble. Based on your information, I can't see any reason why you couldn't have just one user table, with a separate table describing the relationships between the users.

 

[jpo245]

thanks tony, i don't know what this forum would be without people like u!!

 

[jpo245]

actually tony, i have a slightly different but still related question.

Assuming rather than a "subscription feature, i'd like to have a "friend" feature which would go like this:
User A can add User B as a "friend", but the moment this happens, not only does User B become User A's friend, User A also becomes User B's friend.

I'm having problems visualizing how this can be done using one junction table and one usertable. For the previous idea of the "subscription" i can understand, but this one is a bit more complicated, or is it not?

Thanks!

 

[TonyGroves]

It sounds like you could do it by creating two records per two-way relationship, one defining A as a friend of B, the other defining B as a friend of A. This system would also allow you to define one-way relationships if you want.

 

[jpo245]

thanks tony!! you're awesome!

i think thats the best way to go about doing it. 2 records per two way relationship.

i was thinking about just having one record per 2 way relationship, and then switching the foreign key back and forth. but having 2 records per 2 way relationship is much simpler and will give me less of a headache...

awesome!! thanks again!