I am developing an application which requires relationship between entities. Now I am confused whether to use NoSQL for relationship or not? Are they good in retrieval of data? Or Should I stick to RDMS approach?
In short MySQL vs MongoDB.
If you ask me about what NoSQL database to chose, well then you should check out RethinkDB. http://www.rethinkdb.com/faq/
RDMS force you do defined data structures beforehand. Normalize everything (I think people stop at normalization level 2 or 3 because its impossible to know best data structure before app is used by real users), create 2 column tables to store relations (SQL doesn't seem to be the optimal language), put structural knowledge into query (oh but for some relations its good 'huh?) which gets then hidden away by any type of ORM (yeah who doesn't really likes to deal with SQL).
NoSQL is just a term for all types of databases. Key Value Stores, Column stores, big tables, document stores, graph databases and any mix of them.
If you just choose "NoSQL" because its hip and cool, well then you might run into same problems like everyone has with SQL databases. You can use MySQL/MariaDB to mimic data modeling experiences like with document databases or like with key value stores.
Choosing "NoSQL" for performance reasons is also not a good answer. Imagine having a all round vehicle (we can call it SQL) trying to benchmark it against a collection of cars (we can summarize them under term of NoSQL). Starting challenges in different data structure categories and query strategies. Measuring any data structure and read/write query strategy against specialized NoSQL databases is kinda unfair. But its only not fair because engineers have used SQL for too long, trying to solve every problem within SQL databases.
In NoSQL land it is as easy to fall into same traps like it is in SQL land. The biggest difference is DX (Developer eXperience). Because not having the need to press a specific mind model into a normalized data structure, then hiding it behind a ORM -> increases (intangible) productivity. But reaching performance goals can be tough too. Adding caches, using different NoSQL databases for different data structures or accessing strategies can become cumbersome too. Next problem is you have different variants of same data spread across whole architecture. But if each architecture and its shards is chosen wisely, then it still can be better experience than with any pure SQL database.
I think the key to this query lies in ' ... developing an application which requires relationship between entities.'
In general 'NoSQL' systems do not support SQL joins, which is what makes it possible to retrieve 'related' data from different tables in a RDMS database. You can have 'related' data in NoSQL systems of course, but retrieving it is not as efficient or easy as just writing an SQL query.
Then you have to consider scale - are you looking to store a HUGE number of items in a single table.
Then here is the nature of the data - is it easy to break this data down into nice related groups of items ?
Then there's the use case - how do you plan to query this data ?
There is a lot to consider, but my starting point would be that if my app depends a lot on intricate relationships, then I'd definitely go down the relational route; but if my data is not so well structured and my use case requires that I'm able to retrieve a lot of semi-structured data items in one go, then I'd think NoSQL.
Short answer that I've used myself in the past:
If you need to maintain a lot of semi-complex relationships between different entity types, my recommendation would be MySQL.
If you're storing fewer primary entity types and using the relations more for describing the primary entities, then consider MongoDB and storing the data with the document.
If you're feeling frisky and your'e relationship model AND your querying needs are crazy complex (friend of friend stuff), try looking at graph databases like Neo4J, ArangoDB or OrientDB.
If you REALLY want to have some fun: go polyglot. Select 2 or 3 databases that address the different needs of your data and combine them via your application layer. For instance:
Shahriyar Imanov
"Heroism on command, senseless violence, and all the loathsome nonsense that goes by the name of patriotism - how passionately I hate them!" - Albert Einstein
NoSQL is good when you want speed, and thus denormalized data is its motto, as NoSQL is used to store documents, not entities we are used to have in RDBMS. Whenever you need to normalize and dedupe your data, RDBMS is a must.
NoSQL techs generally support referencing, but they are performance-prohibitive: thus referencing is very rarely used with docs.
Overall: NoSQL = denormalized, duped data for fast retrieval, think of it as middleware of data storing SQL = normalized, deduped data storage where your real data is stored for long-term; think of it as REAL data-source
So, best way to go is to use both, according to their purpose.