Is MongoDB the right choice in my case? [closed]

Question

I'm going to build my first real project in Rails that consist in a web app made of 3 main parts:

• The static part where no database is used
• The User registration part which will require a database and I can use MySQL since each user's row will have the same fields
• The "App" where users will be able to create, organize, edit... items in collections and share them with other users

There will be several item types and each one will have different options for example I may have "video" items with the following options:

• id
• user_id
• collection_id
• title
• platform (if embedded)
• url (if embedded)
• file name (if hosted on my app)
• file size (id hosted on my app)

and "map" items:

• id
• user_id
• collection_id
• title
• platform (google maps, bing maps...)
• location
• url
• map size

As you can while for users I may use MySQL for items the flexibility of MongoDB may be useful since each item may need different options then another item

Up to now I've always used PHP and MySQL (always on shared hosting for small projects) and scalability is a totally new word for me.

I have time to learn but I would like to be able to do something concrete in something like 1 month.

I've read a lot about MongoDB and NoSQL vs RDMS and MySQL and after trying it I have to say that I like how MongoDB works: no tables, no rows and its documents JSON like so:

• In my situation what would you raccomend? why?
• About scalability there may be problems with MongoDB? if yes when (in term of DB size) and may these problems slow down my app considerably?

Edit: how the app will work

Since many asked this is how I'd like the app to work:

1. A user sign up
2. He's logged in
3. He create his first collection iside which he can create infinite items
4. Items are of various type and each type need different data to be saved in the database and type of items may be added or modified

Users may create other collections and items inside it.

So we have CRUD for collections and items inside of them and each collection/item is referred to a specific user

The main problem with MySQL is that it hasn't a flexible schema, there is way to solve this (a workaround?)?

Thinking about NoSQL the only doubt I have is about join, for example given a certain colection I want to retrieve datas related to the User with id = user_id field in the collection

EDIT: Idea to keep using MySQL

Create a field in the "items" table with optional settings, each setting divided by a | or another symbol.

Then I will save somewhere a structure of each item optional settings, for example the "notes" item type need two optional settings "colour" and "strange_setting", when I get the data from MySQL i will split the field for optional settings into an array knowing that the first item in the array is for "colour" and so on.

What do you think? there are problem with that solution? do you have other ideas?

Answer 1

We may not be able to help you until you tell us what you intend to do with the app. Relational databases are good for certain things, and NoSQL databases are good for others.

As someone once said to me here on SO:

the relational part of a relational DB is far more optimized than some other parts

It means you can use a relational database also if that seems to fit with your use cases. Don't just go ahead with MongoDB because of its flexibility/scalability. This is the first line about MongoDB on Wikipedia:

MongoDB (from "humongous") is an open source document-oriented NoSQL database system.

Do you really intend to use a document-oriented DB? If there is some graphiness in your use cases, then you may very well go for a graph database like Neo4j. Or you can very well use the best of both SQL and NoSQL together as some people do.

BTW, I also am doing a project in which I use best parts of both SQL and NoSQL.

EDIT: I say once again:

Check out the Neo4j vs Hadoop section on this article. It says:

In principle, Hadoop and other Key-Value stores are mostly concerned with relatively flat data structures. That is, they are extremely fast and scalable regarding retrieval of simple objects, like values, documents or even objects.

Referring to the same article, do you really need a flat data structure for which you are going for MongoDB? This eventually depends upon your detailed use cases, how the step 3 and 4 is going to be performed.

Additionally, you may want to refer these questions:

https://stackoverflow.com/questions/2124274/mongodb-what-to-know-before-using

https://stackoverflow.com/questions/1476295/when-to-use-mongodb-or-other-document-oriented-database-systems

(Check out the top/selected answer of the second question for sure. You're in that dilemma that this might just solve.)

I guess these questions have all the info you wanted to know. In the end, it is you who will have to decide whether it is MongoDb or something else, we can just recommend. The only people who know your detailed use cases are you and your team.

EDIT AGAIN (for the MySQL part): As I've understood it, you are planning on storing something in the db and separating them through a separator. This presents 2 problems:

1. You need to additionally handle any input that will have the separator.
2. The relational storage part of a relational database is far more optimized than the string matching part. I would not go for a scheme where I need to do string matching in a database to get some specific result. Again I am stressing:

   the relational part of a relational DB is far more optimized than some other parts (e.g. string matching)

3. Do not use multivalued attributes. People generally dread them.

Answer 2

I see this question a lot. It always seems to be thought of as either/or. MongoDB is a great new tool. It's also sometimes seem as the shiny tool for everything and that can be a poor choice in my experience.

I think the best combination is definitely BOTH and I would like to commend you for your approach of using mylsql for some parts, such as users, but use MongoDB for other parts as I feel that authentication and authorization is best done with mySQL and there are a ton of examples and modules that do this really well.

For the 'large number of items' piece, that's where you would want to consider using mongoDB if your volume is high, and/or it's mostly reads and/or it's unstructured data.

I would advise not basing your decision on Mongo's schema-less flexibility. SQL and sql-schemas arose from a need to have structured data and be able to perform calculations and transformations that are only possible with such a structure. I learned this from 5 years of working in a data warehouse role. I would only look to MongoBD for the performance issue. If you are or are expecting a high volume of users and requests, say 100,000 users and 20 requests a second, I would use mongoDB, otherwise I would try and stay with sql. In many cases I would use mySQL for low volume, and then, as volume, income and infrastructure support it, switch to Oracle, before mixing in mongoDB. I agree that you should not try to deal with volume problems before you experience them, however if you have a fair idea of where you are heading and don't want to re-write things half-way there it makes a lot of sense to pick the right technologies at the outstart. Just remember that if you really have that high volume there are a huge amount of options and technologies at all levels of the stack that you will look to use.

There are downside to loosely structured data. I use the parking lot analogy here. no dividing lines is great for the first 3 cars that enter, but as more cars enter, a lot of disorganizing starts to happen and trying to get parked or easily count cars and keep lanes free becomes a nightmare. Organizing this takes work up front - marking out lines and dividers and traffic flows, etc. but it pays off. Sometimes things change of course (cars get bigger) and you have to do some changes - repaint lines. Plus just standard down-time for annual repaints and maintenance.

The schema design aspect will probably be the biggest hurdle for traditional mysql users. I think the MongoDb page on schema design helps with that. My final point is that every technology you add into the mix adds complexity. There are often huge advocates for any given piece that will say you "have" to use it, but I have found that a really big factor is just how many pieces there are. It implies more possible points of failure and most of all more of a knowledge base needed for anyone else to have to know to work on it.

fyi Rick Obsorne has a pretty amazing comparison diagram that's quite unique!

Answer 3

I do see a lot of valid arguments here for NoSQL vs MySQL. One missing link though is about scale: If you want to really scale and want to do it with an in house database, you will be needing a LOT of knowledge about databases. There are too many horror stories out there where people have failed in trying to implement a system that will scale infinitely.

If you really choose to go the NoSQL route (and are ready to take the costs that come with it - like no joins), Do consider AWS DynamoDB (http://aws.amazon.com/dynamodb/). Here you can forget about the whole database scaling part of it, and concentrate on your application. Good luck.

Disclaimer: I am a developer in AWS DynamoDB team, but I truly believe in our product. Do try it out:)

Answer 4

So, your design gets to save into your database two different kind of objects:

• User object (which have always the fields).
• Apps objects (which can have different fields). An app will belong to one user only.

A collection could or not me made as a different object, as is just a tag to group different apps. For the sake of argument, let's say there are no collections and users have just a list of applications.

While I think is achievable on MySQL, in MongoDB you'll have a greater flexibility in terms of the structure of app objects, and probably it will map more naturally your representation into the database, making the code simpler.

In MySQL you'll have problems dealing with different formats for different apps, but it is possible. Some ideas:

• You can create an intermediate table with all the common info between all the objects (id, user_id, title, etc), then the type, so you can search for it on another table with only the non-common fields for that format (e.g. file_name and file_size for files). You'll need to create a different table for each different format. If both tables are indexed by app_id (Primary key), it will be fast enough, as accessing a table by an indexed value is fast.
• You can encode the data in some format and store standardized. E.g., encode the non-common data in JSON as a string and store it on a VARCHAR field. Be careful with the size of that field so you don't run out of space. The format can be complex (JSON) or simple (just values separated by commas)
• You can create different "generic" fields, something like int1, int2, str1, str2, and define that str1 for an app type is "file_name" while for a different type could be "location".

On MongoDB, it could be as simple as just using two MongoDB collections, one for the users and another one for the apps. Assuming some sort of limit (which is not the case, as you described, but just for saying), you could even store the apps inside the user object, as a list. Storing and retrieving the data is more natural, as you can store any kind of object, no matter which are the fields. You can search by user_id to get all the apps that belongs to a user. On MongoDB you loose anyway the possibility of doing join queries, but in this case I think the basic queries will be retrieving the user and retrieving the apps related to the user. If you plan to do a lot of stuff like "give me the users who have more than two collections with three applications or less on each", you'll have to generate it not as a join query, but as a process in code, and will be less natural than in a relational database and can take more time to process. If you want to search for parameters (e.g. give me all the apps that belong to a particular user; give me all the apps that are of type X), that's quite easy on MongoDB and don't need to use joins.

I'm not sure about the support of MongoDB on Rails. I have use it in Python and JavaScript.

EDIT: Added comment about time when accessing two tables and another MySQL option

Answer 5

I would say use the technology you know best, especially if it is a real project and you want to push it out fast. Using MySQL and Mongo will both come with its own benefits and headaches. Having worked with both, I would also add it is not very difficult to migrate from MySQL to Mongo if your follow good design principals.

Having that said, one good reason to go with MongoDB in your case is your data. As you have mentioned, you will have several different types of entry for your collections: map, video, and so forth. If you were to implement this using RDBMS, you have 3 approaches:

• table-per-type: each table contains columns specific to each type of objects

  Disadvantages: N query to search across all data types.

  Advantages: good OO design, easily to maintain

• single table: one huge table containing all possible attribute for all types, with most of them null for any particular entry

  Disadvantages: Change to any object will require alter table, painful once table becomes large. Difficult to maintain.

  Advantages: Easy to implement.

• core table with meta-data: you have a single table with the core attributes, say title, dates, and a metadata table with key-value pairs for additional attributes

  Disadvantages: Two query to get all data for a single object.

  Advantages: Extremely flexible, not very difficult to implement.

I have used each of these approaches before, and I can say none is as natural working with Mongo. Your data will probably look something like this:

{_id:"collection1",
 name:"My first Collection",
 owner: "user123243342",
 entries: [
    {type:"video",
     url: "http://www.youtube.com/234324",
     tags: ["roadtrip", "fun", "camera"]
     },
    {type:"map",
     coordinates: [LOC: [38, –102], LOC: [43, –33], LOC: [228, –102]],
     description: "Road trip to nowhere",
 ]
}

But you won't really have to worry about the schema design, as your domain objects can be directly persisted as such. MongoDB essentially is your object store which you can query against.

Noticed I have left out any discussion about the performance comparison between MySql and Mongodb. While you should always keep performance in mind, you won't be able to effectively making decision unless you know the data access pattern. Any good project will probably go through a few iterations of refactoring as it grow and new challenges emerges. Don't worry about performance pre-maturely and pick the tool you know best about and start coding.

Edit

To respond to your specific question on using MySQL and keeping attributes in the same field using "|". Don't do this. This approach will give you more problem than it solves. First of all, you will not be able to query against individual attributes using MySql. Second it adds too much complexity to your data accessing layer. Use either the type-per-table or the meta-data approach instead. If you worked with WordPress before, it uses the meta-data approach:

• user table + usemeta for user
• post table + postmeta table post

This makes the data structure extremely flexible and still query-able with reasonable speed.

Answer 6

The article below provides good results comparing MySQL and MongoDB in terms of select, fetch and insert, considering the amount of data in the database and the amount of retrieved data. The results shows great perfomance for MongoDB regarding the "inserts", but the other cases MySQL wins. See below:

http://www.moredevs.ro/mysql-vs-mongodb-performance-benchmark/

I had an experience using MongoDB that I think it was a good solution. I used it to insert thousands of collections every day. Combined with Solr solution (cache solution, updated once per day), I can retrieve the MongoDB data by the collection id when needed, so I don't need selects on the fly. So, considering you have to deal with lots of inserts and don't needing to care about to select and fetch, MongoDB could be a great idea, it will depends on each case and to do good analysis.