Need advice from MongoDB experts/developers before on switching from RDBMS

Question

We have these tables in SQL2005:

• albums: video album details (category, title, tags, date, author, likes, views etc
• tags: album tags & ordered alphabetically(to find all albums w/ a particular tag)
• ratings: stores the id of the users who rated album(to prevent duplicate rating)
• comments: stores all comments on the album
• comment_ratings: id of the users rated a comment(to prevent duplicate rating)
• comment_replies: all the replies to a comment with date and poster info
• comment_reply_ratings: id of the users who rated a reply(to prevent duplicate rating)

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

1) Get top 10 most liked albums( title, thumbnail, views, likes, author & date) with paging. If user clicks next, get next 10 most liked album & so on

2) Get top 10 most viewed albums with paging.

3) Get top 10 most discussed albums with paging.

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

5) Get all albums by a given user (title, thumbnail, views,likes, author & date)

6) Get details of a specific album & show just top 10 comments with paging. Next will load next 10 comments and so on.

7) Get list of related albums. Relation will be done via album tags or album title

8) Searching by keywords would search the title or tag field of album.

9) When someone clicks a tag, get the list of all albums with that tag

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

11)Get comments ordered by ratings, date etc

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

Thanks for reading. God bless.

Answer 1

I suggest 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've moved comments into separate collections (instead of embed it in album) because for now in mongodb hard to update documents with more then one level of nesting.

Now queries:

1) Get top 10 most liked albums( title, thumbnail, views, likes, author & date) with paging. If user clicks next, get next 10 most liked album & so on

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

2) Get top 10 most viewed albums with paging.

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

3) Get top 10 most discussed albums with paging.

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

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

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

5) Get all albums by a given user (title, thumbnail, views,likes, author & date)

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

6) Get details of a specific album & show just top 10 comments with paging. Next will load next 10 comments 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 list of related albums. Relation will be done via album tags or album title

Please clarify

8) Searching by keywords would search the title or tag field of album.

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

Note: Probably need to store tag twice: in lower case for searching, and as is for displaying

9) When someone clicks a tag, get the list of all albums with that tag

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

Note: Using $in oprator you can search by multiple tag names.

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

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

11)Get comments ordered by ratings, date etc

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

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

Please clarify

I think that now you see that you can move your relational database to MongoDB, and believe your application will be amazing fast with mongodb and above schema ;).

Hope this help.

P.S: If sql 2005 than i suppose you use some .net language?