zidZ
Convex Community•3y ago•
12 replies
zid

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:

defineSchema({
  users: defineTable({
    // user fields
  }),
  friends: defineTable({
    lowerUserId: v.id("users"),
    higherUserId: v.id("users"),
    // any additional fields
  })
  .index("by_userIds", ["lowerUserId", "higherUserId"]),
});


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:

defineSchema({
  users: defineTable({
    // user fields
  }),
  friends: defineTable({
    userId: v.id("users"),
    friendId: v.id("users")
  })
  .index("by_userIds", ["userId"), // fetch all of users friends
  .index("by_userIds_friendId", ["userId", "friendId") 
});


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.
Was this page helpful?