TTC
Theo's Typesafe Cult•2mo ago
johnny the fifth

how do i prevent duplication of rows in my friends table ?

i am working on adding friends feature in my application i am using mysql database , with prisma orm i have a friends table and a users table as the name implies the user table stores the users , friends stores friend records the schema of both tables are as follows 
model Users {
  id       
  username    
  email       
  uuid             
  status      
  friends     Friends[]
}
model Friends {
  id             
  username       
  friendId       
  email          
  friend_name    
  friend_request_enum
  Users  Users? @relation(fields: [userId], references: [id])
  userId Int?
}
model Users {
  id       
  username    
  email       
  uuid             
  status      
  friends     Friends[]
}
model Friends {
  id             
  username       
  friendId       
  email          
  friend_name    
  friend_request_enum
  Users  Users? @relation(fields: [userId], references: [id])
  userId Int?
}
when a friend request is sent a record is created in the friends table the here is what happens in my database as an example we have two users userA:john , userB:sara , john sends a friend request to sara a row is created as following : 
username :john
  friendId:sara's uuid 
  email: john email 
  friend_name:sara  
friend_request:"SENT" 
username :john
  friendId:sara's uuid 
  email: john email 
  friend_name:sara  
friend_request:"SENT"

if sara accepts the friend request is set to ACCEPTED when i fetch the user Data i am including the friends relation in the query let's say that i fetch the userData of the userB : sara that accepted the friend request the problem is friends field returned from the query is empty after some digging i discovered that's because the row created was created by the sender userA:john therefore when i query the user Data that accepted the friend request userB : sara i am getting an empty friends field , if i query userA:john i get friend list with one row that shows sara as the friend , i attempted to insert a another row in the friends table where the sender in this case is sara and the reciever is john problem is i am gonna have two rows for each friendship which means more space in my database i wanna have only one row for each friendship is there any way to achieve that + is there anything wrong with having two rows for each friendShip ?
5 Replies
peculiarnewbie
peculiarnewbie•2mo ago
what i would do is instead of having a Friends table, i would have a Friendships table wih id, senderId, receiverId, accepted or something like that. So a self-referencing many-to-many relationship
johnny the fifth
johnny the fifthOP•2mo ago
how would that look like schema wise + can you give me an example of how would that be implemented
peculiarnewbie
peculiarnewbie•2mo ago
sorry i don't use prisma but it would be something like 
model Users {
  id       
  username    
  email       
  uuid             
  status      
  firendships Friendships[]
}
model Friendships {
  id
  senderId
  receiverId
  accepted
}
model Users {
  id       
  username    
  email       
  uuid             
  status      
  firendships Friendships[]
}
model Friendships {
  id
  senderId
  receiverId
  accepted
}
now whenever someone send a friend request, you'd create a row in Friendships with the sender and receiver ids (these should be foreign keys but idk how that works in prisma), then add the id of that row to each user so now you can query every "friendship" that a user has
johnny the fifth
johnny the fifthOP•2mo ago
can i have more than one foreign key in the same table
peculiarnewbie
peculiarnewbie•2mo ago
yeah but it would reference the same thing

Did you find this page helpful?