Why store all reference ids?

user_7444925 0
Viewed 46

lets say I have one to many relation with Post and Comment models.

When embedding is not used he suggested way to handle data relation is:

const PostSchema = new Schema({
  _id: Types.ObjectId,
  comments: [{type: Types.ObjectId, ref:'comment'}]
})

const CommentSchema = new Schema({
  _id:Types.ObjectId
})

Wouldnt the schema design below be more appropriate?(storing postId as foreign key in Comment). And also is there a name for such relation method?

const PostSchema = new Schema({
  _id: Types.ObjectId
})

const CommentSchema = new Schema({
  _id:Types.ObjectId,
  postId: Types.ObjectId
})

To get a post and all its comments I would simply do:

let post = await Post.find({ _id: postId });
post.comments = await Comment.find({ postId });

With the first method, every time a new comment is created, the post document must be updated as well. And if we want to guarantee that commentId is added to comments array, we should use transaction, which makes this update even more costly.

However with the second approach, we only have to create comment. Now the downside is that I will not be able to use $lookup, but performance wise, would it make much of a difference? $lookup is not like JOIN in relational databases, where JOINs are done in a single database operation. So using $lookup or simply querying by comment's postId(indexed) to "JOIN" the data would not make much of a difference in terms of performance.

mongodb
2 Answers

your example can be found in the documentation applied to a different domain model (comments are books and posts are publishers).

When using references, the growth of the relationships determine where to store the reference. If the number of comments per post is small with limited growth, storing the comment reference inside the post document may sometimes be useful. Otherwise, if the number of comments per post is unbounded, this data model would lead to mutable, growing arrays...

To avoid mutable, growing arrays, store the post reference inside the comment document

The document schema should be oriented on your use case requirements, which define your queries. As you identified, it makes sense to store the post reference on the comment, so you don't have to update the post every time you create a new comment. So without more information about growth rates, access frequency and other loading scenarios it sounds like option 2 makes more sense in this context.

user_15150520

The first approach is unusual but it can work in some situations depending on query needs.

user_95409250
Related