Modeling friends and followers in an RDBMS

Question

I am trying to choose the best way to model the relationship of records in a relational database. This is a classic model for friends and friends:

~~~~

A user can have zero for many friends.
A user can have from zero to several followers.

Friends and followers are the users themselves.

~~~~~

What is the best way to simulate 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
)