I could prove that I’m a dinosaur and say “are you out of your mind — No SQL?” But then, I would be the one out of my mind.
I’ve always used relational databases and SQL. In fact, SQL could probably be called my second language. And while I don’t have the gripes that others may have with the language itself [http://www.kellblog.com/2010/02/24/the-database-tea-party-the-nosql-movement/], relational databases do come with a bag full of limitations and challenges. I’ll tell you my gripe.
My biggest gripe is that I believe in object oriented programming, and RDBMS doesn’t play nicely in the OO sandbox. Now of course, you could write your own data tier that calls stored procedures, or that has prepared statements. But this is asking developers to write in a second language for your application. This leads to ORMs, and for anybody who hasn’t worked with me, know now that I hate them for anything beyond simple CRUD operations.
But the biggest problem is effective modeling. You cannot effectively model some object or hierarchical relationships with RDBMS. For example, try to model n-deep dynamic menus in a relational database. Each menu node has a parent, and each menu node falls within some order relative to the other items under the same parent.
Example Menu Hierarchy
This is actually fairly easy to model in an RDBMS, but it is not very effective. Each node has a parent node (except for the root node, which I left out for the sake of this example), and each node has a numeric ordering.
Data Model for a Menu in a RDBMS
So here comes the problem. What’s the SQL for selecting out your menu structure? Since you do not know how deep the structure is (n-deep), you could do this with a crazy loop and some recursion.
- Get the root node [SELECT * from MenuNode where nodeParentNode is null]
- Get all of the first level nodes [SELECT * from MenuNode where nodeParentNode=0 ORDER BY nodeOrder ASC]
- For each node returned, get it’s children, recursively
For the example menu hierarchy above, this would result in 1 SQL call to get the root, 1 call to get its children, 1 call for each of those 4 children to look for their children, two calls to get the children of D3a and D3b, two calls to get the children of D3bA and D3bB, and one call to find the children of D3bBi. SQL round trips are expensive, and this is a lot of calls for a small menu. This does not scale at all. And this doesn’t even address the problem of maintaining order within each level of the hierarchy. Of course, there are other ways to skin this cat. nodeOrder could be replaced with nodeAbove, using more of a linked list type of structure, but that doesn’t work well either. It would probably be more efficient to grab all the nodes in one sql call, and put them back together as you load them into a proper data structure, which in and of itself will take processing and time.
The bottom line is that it’s not a natural fit.
So, NoSQL people, tell me how you would do this using other tools? CouchDB? MongoDB? Cassandra? MarkLogic? I presume this problem has been largely solved. I still don’t believe that RDBMS is a bad tool. You need to use the right tool for the problem you’re trying to solve, and be careful to minimize the total number of data persistence tools in your organization.