Nosql database design - MongoDB

Question

I am trying to build an app where I just have these 3 models:

• topic (has just a title (max 100 chars.))
• comment (has text (may be very long), author_id, topic_id, createdDate)
• author (has just a username)

Actually a very simple db structure. A Topic may have many comments, which are created by authors. And an author may have many comments.

I am still trying to figure out the best way of designing the database structure (documents). First I though to put everything to its own schema like above. 3 Documents. But since this is a nosql db, I should actually try to eliminate the needs for a join. And now I am really thinking of putting everything to a single document, which also sounds crazy.

These are my actually queries from ui:

• Homepage query: Listing all the topics, which have received the most comments today (will run very often)
• Auto suggestion list for search field: Listing all the topics, whose title contains string "X"
• Main page of a topic query: Listing all the comments of a topic, with their authors' username.

Since most of my queries need data from at least 2 documents, should I really just use them all together in a single document like this:

Comment (text, username, topic_title, createdDate)

This way I will not need any join, but also save i.e. the title of topics multiple times.. in every comment..

I just could not decide.

I appreciate any help.

Answer 1

You can do the second design you suggested but it all comes down to how you want to use the data. I assume you’re going to be using it for a website.

If you want the comments to be clickable, in such that clicking on the topic name will redirect to the topic’s page or clicking the username will redirect to the user’s page where you can see all his comments, i suggest you keep them as IDs. Since you can later use .populate(“field1 field2”) and you can select the fields you would like to get from that ID.

Alternatively you can store both the topic_name and username and their IDs in the same document to reduce queries, but you would end up storing more redundant data.

Answer 2

Revised design:

The three queries (in the question post) are likely to be like this (pseudo-code):

• select all topics from comments, where date is today, group by topic and count comments, order by count (desc)
• select topics from comments, where topic matches search, group by topic.
• select all from comments, where topic matches topic_param, order by comment_date (desc).

So, as you had intended (in your question post) it is likely there will be one main collection, comments.

comments:

  date
  author
  text
  topic

The user and topic collections with one field each, are optional, to maintain uniqueness.

Note the group-by queries will be aggregation queries, for example, the main query will be like this:

db.comments.aggregate( [
  { $match: { date: ISODate("2019-11-15") } },
  { $group: { _id: "$topic", count: { $sum: 1 } } },
  { $sort: { count: -1 } }
] )

This will give you all the topics names, today and with highest counted topics first.