MongoDB experts / developers should be advised before switching from RDBMS

Question

We have these tables in SQL2005:

• albums : information about the video album (category, title, tags, date, author, likes, types, etc.
• tags : album tags and sorted alphabetically (to find all albums with a specific tag)
• rating : saves the identifier of users who rated the album (to prevent duplicate ratings)
• comments : saves all comments to the album
• comment_ratings : user id rated comment (to prevent duplicate rating)
• comment_replies : all responses to the comment with the date and poster Information
• comment_reply_ratings : identifier of users who rated the response (to prevent duplicate ratings)

Can this type of structure be created in MongoDB allowing the following operations / queries with equal / better performance?

1) Get the 10 most popular albums (title, sketch, views, author and date) with paging. If the user clicks the "Next" button, add the 10 most popular albums, etc.

2) Get the 10 most popular paging albums.

3) Get the 10 most talked about paging albums.

4) Get a list of recently created paging albums, but up to 100 albums

5) Get all albums of this user (name, thumbnail, views, author and date)

6) Get detailed information about a particular album and show only 10 paging comments. Next, the following 10 comments will be loaded, etc.

7) Get a list of related albums. Communication will be done using album tags or album name

8) Keyword search will search for the name or field of the album tag.

9) When someone clicks on a tag, get a list of all albums with this tag

10) When someone clicks a category link, get a list of 10 categories albums

11) Get comments sorted by rating, date, etc.

12) Can the order in which new entries made in a document be controlled?

Thank you for reading. God bless.

Answer 1

I suggest the following structure:

Album { Id, UserId, Title, Category, Tags (list of tag names for fast access and for searching), Ratings (user ids, use $addToSet), Likes (user ids, use $addToSet), ViewsCount, (probably just integer value,) RatingsCount (use $inc to increment this field once someone vote for album), CommentsCount (use $inc everytime when someone post comment), LikesCount (use $inc everytime when someone click 'Like it') } Comment { Id, AlbumId, Text, CreatedDate, Ratings, RatingsCount, Replies { (collection of comment replies) Text, CreatedDate, Ratings, RatingsCount } } Tag { Id, TagName, AlbumsCount (use $inc: 1 when new album created with this tag and $inc:-1 - once deleted) } 

I moved the comments to separate collections (instead of inserting them into the album), because so far it is difficult to update documents with more than one nesting level in mongodb.

Now requests:

1) Get the 10 most popular albums (title, thumbnail, views, comments, author and date) with paging. If the user clicks the "Next" button, add the 10 most popular albums, etc.

 db.albums.find().skip(0).limit(10).sort( { RatingsCount: -1 } ); 

2) Get the 10 most popular paging albums.

 db.albums.find().skip(0).limit(10).sort( { ViewsCount: -1 } ); 

3) Get the 10 most talked about paging albums.

 db.albums.find().skip(0).limit(10).sort( { CommentsCount: -1 } ); 

4) Get a list of recently created paging albums, but up to 100 albums

 db.albums.find().skip(0).limit(100).sort( { CreatedDate: -1 } ); 

5) Get all the albums of this user (name, thumbnail, views, likes, author and date)

 db.albums.find({UserId: someUserId}) 

6) Get detailed information about a particular album and show only 10 paging comments. Next, the next 10 comments will be loaded and so on.

 album = db.albums.find({_id: someAlbumId}); comments = db.comments.find({AlbumId: someAlbumId }).skip(0) .limit(10).sort( { RatingsCount: -1 ,CreateDate: -1 } ); 

7) Get a list of related albums. Communication will be via album tags or album name

Please clarify

8) Keyword search will search for the name or field of the album tag.

 db.albums.find( { $or : [ { Title : searchKey } , { Tags : searchKey } ] } ) 

Note. You probably need to store the tag twice: lowercase for search and as shown for display

9) When someone clicks on a tag, get a list of all albums with this tag

 db.albums.find( {Tags : { $in: [tagName1, tagName2]}} ] } ) 

Note. Using $ in oprator, you can search for multiple tag names.

10) When someone clicks a category link, get a list of 10 category albums

 db.albums.find({Category: val }).skip(0).limit(10).sort( { CreatedDate: -1 } ); 

11) Get comments sorted by rating, date, etc.

 db.comments.skip(0).limit(10).sort( { RatingsCount: -1 } ); 

12) Is it possible to control the order in which new records in a document are managed?

Please clarify

I think that now you see that you can move your relational database to MongoDB and assume that your application will grow very quickly with mongodb and above schema;).

I hope for this help.

PS: If sql 2005, than I assume, are you using any .net language?

Answer 2

I see such questions arising all over the Internet. The most common answer you will find is to use the right “tool” for every job, so neither Mongo nor standard RDBMS will match each individual application better than the rest of the database, but in my opinion you should definitely invest some time by learning this new “tool” because it will give you an even better perspective on what suits your thinking and programming style better.

I also recently started using MongoDB after using SQL databases for many years, and I can tell you that it is worth the experience. You can do almost everything with Mongo, definitely everything you described on your list, but be prepared to make some trade-offs in the process. I do not know what language you use. I use PHP, and I think that MongoDB fits PHP much better than any of the SQL databases, in the sense that it organizes the data in JSON format, which is more natural for the PHP tree structure (arrays, classes, etc. ), Which allows you to largely dump the entire array or part of it into the database without doing a lot of “matching” and returning it when you need it. In addition, a scale-free design can give you a little more flexibility on how to organize (or not organize :)) your data. Also, if you want to develop a large application, MongoDB will be much easier to scale than most SQL-DBs.

But for a bit more “rigorous” applications where “consistency” and “durability” are important, I would choose one of the old-fashioned RDMBS databases such as PostgreSQL. In fact, nothing prevents you from using both tools in the same project if you need to. Hope this helps!