Not that classic RDBMS is bad or faulty but mainly because scalability is taken into consideration that NoSQL has emerged. A site with huge traffic, especially social networking site with millions of users will hardly be able to manage with classic RDBMS.
For those who are afraid of writing SQL queries because of Joins(I'm also one of them...omg ordering matters and what not!) need not worry now. Switch to NoSQL! There is no common query language across these databases as is the case with relational db(fortunate or unfortunate?). It runs on Unix. Data can be easily migrated to systems with other operating systems.
NoSQL databases are based on either of the models: key-value stores, graph databases, document store and column store. Some well-known models of NoSQL are:
MongoDB: Any of the languages can be used like Java, C, C++, PHP. It is written in C++. Data interchange format is BSON (Binary JSON).
Cassandra: Written in Java while Thrift is used for external client-facing API. It supports a wide range of languages (Thrift languages). It brings together the best blend of features of Google's BigTable and Amazon's Dynamo. Cassandra was developed and later open-sourced by FaceBook.
HBase: It is built on top of Hadoop. Written in Java. It is used when realtime read/write access to big data is needed.
First two of the databases are based on document store while rest two on column store(aka ColumnFamilies).
For those who believe in 'Greater the difficulty sweeter is the victory', may be NoSQL is not the best choice (since it will ease out many things...no worries of scalability when traffic increases and no Joins!!). What is your choice?