I've been reading a lot of comparisons between NoSQL and SQL. I feel like NoSQL might be the way to go but I'm still uncertain. So here's my example:

I want to be create an inventory system. There will be user accounts with the usual details (email, username, etc.). Users will be able to create stores and lists. Stores can be linked to a list, multiple stores can be linked to the same list.

I think there may be more reads than writes, which leads me towards NoSQL. However, I feel like I may need joins and such, which leads me towards PostGRE (I'd also like arrays).

Given the example is NoSQL the way to go?

For NoSQL I'm looking at Amazon's DynamoDB.

closed as too broad by Pieter B, TheCatWhisperer, gnat, Jörg W Mittag, Kilian Foth Feb 5 '18 at 7:52

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What kind of NoSQL were you thinking about? A key-value database or like a document store? There are so many technologies that are under the NoSQL umbrella that you can't compare SQL to NoSQL. – Pieter B Feb 5 '18 at 0:22
  • @PieterB Thanks for answering. I was planning to go with Amazon's DynamoDB since it's managed and I don't need to worry about sharding and spinning up additional servers myself. – A. Lau Feb 5 '18 at 0:33

Let me just start by quoting Amazon's DynamoDB FAQ

Q: When should I use Amazon DynamoDB vs a relational database engine on Amazon RDS or Amazon EC2?

Today’s web-based applications generate and consume massive amounts of data. For example, an online game might start out with only a few thousand users and a light database workload consisting of 10 writes per second and 50 reads per second. However, if the game becomes successful, it may rapidly grow to millions of users and generate tens (or even hundreds) of thousands of writes and reads per second. It may also create terabytes or more of data per day. Developing your applications against Amazon DynamoDB enables you to start small and simply dial-up your request capacity for a table as your requirements scale, without incurring downtime. You pay highly cost-efficient rates for the request capacity you provision, and let Amazon DynamoDB do the work over partitioning your data and traffic over sufficient server capacity to meet your needs. Amazon DynamoDB does the database management and administration, and you simply store and request your data. Automatic replication and failover provides built-in fault tolerance, high availability, and data durability. Amazon DynamoDB gives you the peace of mind that your database is fully managed and can grow with your application requirements.

While Amazon DynamoDB tackles the core problems of database scalability, management, performance, and reliability, it does not have all the functionality of a relational database. It does not support complex relational queries (e.g. joins) or complex transactions. If your workload requires this functionality, or you are looking for compatibility with an existing relational engine, you may wish to run a relational engine on Amazon RDS or Amazon EC2. While relational database engines provide robust features and functionality, scaling a workload beyond a single relational database instance is highly complex and requires significant time and expertise. As such, if you anticipate scaling requirements for your new application and do not need relational features, Amazon DynamoDB may be the best choice for you.

Are you even expecting a few thousand users? Do you have any concern with a sudden spike to millions of users? You've already stated that you need "complex relational queries" to use the terminology from the quote. Choosing a key-value/document store is not just saying "I don't need those now" but also "and I will never need those".

The quote also paints an overly rosy picture of NoSQL key-value/document stores. The weakened consistency guarantees lead to a significant amount of extra complexity in the application code to get correctness. My strong impression is that many developers using NoSQL key-value/document stores, just pretend that they have these consistency guarantees (or rather, don't realize that they don't) and write subtly broken code. (Cue the multiple Bitcoin exchanges that got "hacked" because they wrote code against NoSQL databases assuming they provided more consistency than they do.) There are some things that you just can't do with a NoSQL key-value/document store without basically manually reimplementing some of the trickiest parts of a relational database.

My general advice is that a relational database should be the default choice. Realistically, a system using a NoSQL data store will almost certainly have (or benefit from) a relational database as well, so the real question is, "is there any reason to also have a NoSQL data store?" The benefits of relational databases is that they are some of the most battle-tested software systems on the planet, are full-featured, and are very unlikely to leave you boxed in a corner where implementing certain functionality is just "impossible". Read-heavy loads are not problematic for relational databases, but even if they were the solution to that would be caching. You may even use a NoSQL key-value/document store for that cache! That would be a very good use of a NoSQL solution.

Relational database are likely to be completely adequate for many users needs performance-wise. They simplify and speed up development by presenting a much simpler consistency model and providing more features out-of-the-box. There's likely to be some data where consistency is important and latency isn't important; these are well-served by a relational database. There is also likely to be data where latency is more important and up-to-date consistency less so; caching handles this well and NoSQL solutions often shine here (assuming normal HTTP caching doesn't suffice). If your system does need to scale, you are most likely looking at a hybrid system, not a transition to a different data storage technology altogether.

(There are some application domains where it makes sense to design the system from the get-go for weak consistency to achieve low-latency, e.g. online, multiplayer, first-person shooter games. But this isn't most systems, or at the very least it's a choice between paying for a complicated low-latency design now or paying for it later when you have more information about the requirements and load [and likely more money and expertise].)

  • thanks for the answer, I think I'll stick with relational for now. – A. Lau Feb 12 '18 at 0:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.