How to represent a self to self relationship in rdbms?

Question

Hi,

Hopefully this is a silly question but my brain is fried and I can't think of how to do it right now.

I'm messing around trying to make a social networking thingy and will need to keep track of a person's "followers". I have a user table made already, but how do I represent the idea of users being associated with one another? This is a one way relationship btw, so if person a "likes" person b, the reverse is not necessarily true.

The only thing I can think of is a table which just has a list of one-to-one relationships, so:

user a -> user b  
user a -> user c  
user a -> user d  
user b -> user d  
user b -> user c  
...

but this seems off to me.

Thanks

Simon

edit: maybe a follower on twitter is more analogous to what i'm trying to do

Answer 1

Perhaps a table that looks like:

RelationshipId    Follower Target
Int               Int      Int

Where Follower is the Id of the UserA, and Target is the Id of UserB, and UserA is following UserB.

Answer 2

Create a table that holds only follower id information like so:

user_id | follower_id

That should be it. so take for instance user #56.

he would probably have rows like this in the follower table:

56 | 53453
56 | 323
56 | 463

Just add a unique constraint on both columns so that he cannot friend someone twice.