When Shouldn't You Use a Relational Database

When shouldn't you use a relational database?

In my experience, you shouldn't use a relational database when any one of these criteria are true:

• your data is structured as a hierarchy or a graph (network) of arbitrary depth,
• the typical access pattern emphasizes reading over writing, or
• there’s no requirement for ad-hoc queries.

Deep hierarchies and graphs do not translate well to relational tables. Even with the assistance of proprietary extensions like Oracle's CONNECT BY, chasing down trees is a mighty pain using SQL.

Relational databases add a lot of overhead for simple read access. Transactional and referential integrity are powerful, but overkill for some applications. So for read-mostly applications, a file metaphor is good enough.

Finally, you simply don’t need a relational database with its full-blown query language if there are no unexpected queries anticipated. If there are no suits asking questions like "how many 5%-discounted blue widgets did we sell in on the east coast grouped by salesperson?", and there never will be, then you, sir, can live free of DB.

Would relational databases scale as well (or better) than their NoSQL counterparts if we drop the relationships?

I don't find that inter-table relationships are the main limiter for scalability. I use queries with joins regularly and get good scalability if indexes are defined well.

The greater limiter for scalability is the cost of synchronous I/O. The requirements of consistency and durability -- that the DBMS actually and reliably saves data when it tells you it saved data -- is expensive.

Several NoSQL products that are currently in vogue achieve great performance by weakening their consistency and durability guarantees in their default configuration. There are many reports of CouchDB or MongoDB losing data.

There are ways you can configure those NoSQL products to be more strict about durability, but then you sacrifice their impressive performance numbers.

Likewise, you can make an SQL database achieve high performance like the NoSQL products, by disabling the default features that ensure data safety. See RunningWithScissorsDB.

PS: If you think document-oriented databases are "cutting edge", I invite you to read about MUMPS. Everything old is new again. :-)

Why are relational databases needed?

I will come at this from a different angle.

I work at a place where we had a database that had no foreign key constraints, default values, or other data checks whatsoever in their initial records database. The lead engineer's excuse for this was something similar to what you have described above. "The application will ensure the referential integrity".

The problem is, we did not have a standard data layer (like an object relational mapping) over the top of the database. We had multiple programmatic sources that fed into the same tables. It was funny because after a while, you could tell which parts of the code created which rows in the table. Sometimes the links lined up, sometimes they didn't. Sometimes the links were NULL (when they shouldn't be), and sometimes they were 0. We even had a few cyclic records which was fun.

My point is, you never know when you are going to need to write a quick script to batch import records, or write a new subsystem that references the same tables. It behooves us as programmers to program as defensively as possible. We can't assume that those who come after us will know as much (if anything) about how our schema should be used.

Should I choose relational or non-relation database for social-network like app

Step 1) Create your design using whatever technology you are strongest with.

Step 2) Release your social network, begin on researching non-relational databases and master whichever you feel most comfortable with.

Step 3) Refactor your data tier so you could potentially replace MySQL quickly and easily with your newly learned DB technology.

Step 4) Wait for your website to become so big that the need to replace MySQL comes around and begin to plug the holes.

I know this seems kind of cheeky, but really my point is just release your software and start to worry about scale etc. when it actually becomes a concern.

Are relational databases a poor fit for Node.js?

No, there isn't a technical reason. It's mostly just opinion and using NoSQL with Node.js is currently a popular choice.

Granted, Node's ecosystem is largely community-driven. Everything beyond Node's core API requires community involvement. And, certainly, people will be more likely to support what aligns with their personal preferences.

But, many still use and support relational databases with Node.js. Some notable projects include:

• mysql
• pg
• sequelize

---

Related Topics