Table of undirected connections between users, more simple solution possible?

[foolo]

Question

Hi. I'm trying to set up a schema (for a PostgreSQL database) where two records in a table can either connected or not.
For the sake of simplicity, let's say that it's about users who can be friends or not.

I'm thinking of something like this: (the below is not a valid schema)

model User {
  id         Int          @id @default(autoincrement())
  username   String       @unique
}

model Friendship {
  id      Int  @id @default(autoincrement())
  userA   User @relation(fields: [userA_id], references: [id])
  userA_id Int
  userB   User @relation(fields: [userB_id], references: [id])
  userB_id Int
}

But since Prisma always requires opposite relations (see #21380 ), I need to add two very confusing fields userA_in_friendships and userB_in_friendships, which are basically listing in what friendships a certain user is userA and userB respectively.

So the best I have come up with is this:

model User {
  id         Int          @id @default(autoincrement())
  username   String       @unique
  userA_in_friendships Friendship[] @relation("FriendshipA")
  userB_in_friendships Friendship[] @relation("FriendshipB")
}

model Friendship {
  id      Int  @id @default(autoincrement())
  userA   User @relation("FriendshipA", fields: [userA_id], references: [id])
  userA_id Int
  userB   User @relation("FriendshipB", fields: [userB_id], references: [id])
  userB_id Int
}

which seems very complex for a seemingly simple contruct.

My question is whether there is a simpler or more comprehensible way to configure this?

Information about Prisma Schema, Client Queries and Environment (optional)

• Database: PostgreSQL

[ludralph]

Hi @foolo 👋

Have you considered modeling the relationships using a Many-to-Many Self relation. For example:

model User {
  id        Int    @id @default(autoincrement())
  username  String @unique
  friends   User[] @relation("UserFriends")
  friendsOf User[] @relation("UserFriends")
}

This relation expresses the following:

• A User can have zero or more friends
• A User can be friendsOf zero or more Users

This many-to-many-relation is implicit. If you need the relation to hold other fields, you can create an explicit many-to-many self relation as well.

[foolo]

I should have added that this is for an SQL database, (Postrgres). I don't think this variant will work with SQL databases, right? I guess I still would need a junction table.

[ludralph]

This will work for Relational Databases (Postgres, Mysql etc). Do take a look at the documentation.

[foolo]

Ahh, you are right. That resolves my question, thanks a lot!