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!
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
)