Question about designing friend request / follow system with relational tables
I'm using Prisma on top of PostgreSQL. In my app, I have a
Follow
table, denoting follow relationships, and a FollowRequest
table, denoting pending follow requests.
I've enforced that in both the Follow
and FollowRequest
table, the permutation of two users is unique. So we can't have multiple duplicate FollowRequests
.
I want to write a function for my expres s server that can reject a follow request. I have the user context already. I see two ways of writing it:
1) Get the followRequestId
from the request. Lookup the FollowRequest
row in Prisma. If the user context matches with the FollowRequest
, delete the row. This is two sequential database interactions.
2) Get the sourceUserId
and the targetUserId
in the request (basically the person who made the follow request, and the person who was requested). I can then check if the user context matches the targetUserId
, but this time I don't need a database read to do so. I can then perform a Prisma delete with the unique combination (sourceUserId, targetUserId)
. This is just one database interaction.
The second one feels a bit odd, because I'm not working with the unique followRequestId
which is part of my FollowRequest
table. I'm leveraging the fact that (sourceUserId, targetUserId)
is unique.
Are there recommendations for how to approach this? I'm asking here because I'm not sure if I'm missing considerations on efficiency / how Prisma works.2 Replies
For (1), instead of doing a read first and then a delete, could you not specify the same
where:
clause in the delete
as you would for the find
? You can then check the result to see if anything got deleted.hey, yes I think so! thanks, I am still learning SQL