bidirectional relationships
Trying to determine the best schema for "friends" (many to many) feature.
As someone who's not experienced on the SQL side of things, would love to know if im missing anything and/or there's a better approach altogether.
The main features/capabilities for the friends feature will be to fetch a list of friends, as well as to update any meta data between these friends.
Fetching a users friends is much more frequent.
Bidirectional Approach:
Pros:
Only one document per friendship, which means writes are atomic and there is less data to keep in sync.
May be more intuitive from a data modeling perspective as a friendship is inherently a bidirectional relationship.
Cons:
More complex queries are required to find all friends of a given user.
The sorting mechanism for user IDs must be strictly followed on every operation to ensure data consistency.
Unidirectional Approach:
Pros:
Simpler and more efficient queries for fetching all friends of a user.
Cons:
Requires two documents for each friendship, doubling the write operations.
More complex logic needed to ensure both documents are kept in sync, despite transactions guaranteeing atomicity.
8 Replies
I would go with the unidirectional approach, with two relationship documents for each friendship. You shouldn't get inconsistencies because mutations are atomic. What kind of inconsistencies are you worried about? Having two documents makes inserting and fetching simpler, and gives you an extra place to put extra unidirectional fields about the friendship like "best friend" or "nickname"
Thanks Lee! Not worried about inconsistencies (just edited my post). And that latter point is great, thank you!
you might be worried about a buggy mutation creating an inconsistent relationship that doesn't get cleaned up when you fix the bug. if that sounds plausible, you could make a cron that looks at all data and detects inconsistencies, but you shouldn't need that unless you suspect dangling references
no im good, guanteed trnsactions should be more than enough
hmm, i realize that for every friend that I fetch, I need to fetch a document from a separate table. This is mainly due to the fact that many of the fields are dynamic.
is the following the proper/best way to create the query? This is a simplified version as I'll be using the usePaginated hook instead.
Looks good to me. Let us know if you have any issues
Hmm, I am getting an issue here..
TypeError: currResult.page is not iterable (cannot read property undefined)
I see the page property..
Made a workaround, however, not sure if this is stable/safe
Oh yeah i would do
return {...friends, page: friendsStats}
To copy over existing fields that pagination needsHmm, i realized that I need to fetch dynamic properties belonging to the
friend
. This means that with the unidirecitonal approach, i wouldn't be able to efficiently add these specific fields. The limit of friends for each user has not been determined yet, but if need be, the smallest number would ideally be atleast 1000 friends.
With this in mind for the paginated query, i'm thinking of filtering and sorting in javascript...seems like this is the only way? Concerned about performance though