Modeling Friends and Followers in an RDBMS

Question

I'm trying to decide on the best way to model a relationship of records in a relational database. It's the classic friend/follow model:

~~~~

A User can have zero to many friends.
A User can have zero to many followers.

Friends and followers are both Users themselves.

~~~~~

What's the best way to model this?

Thanks!

Answer 1

Users (UserId, ...)
Subscription (Subscriber, Publisher)
Friendship (FirstUser, SecondUser)

CREATE TABLE Users (
    UserID int not null primary key,
    ...
)

CREATE TABLE Subscription (
    Subscriber int not null references Users(UserID),
    Publisher int not null references Users(UserID),
    constraint ck_NotEqual check (Subscriber <> Publisher)
)

CREATE TABLE Friendship (
    FirstUser int not null references Users(UserID), 
    SecondUser int not null references Users(UserID),
    constraint ck_Order check (FirstUser < SecondUser) -- since friendship is reflective
)