When NoSQL is better than a relational database

Relational databases are known for their reliability, data integrity and ability to optimize queries joining many tables. They often have ORM frameworks (like Entity Framework, Hibernate, Dapper …etc.) that help do these queries from programming languages.

Designing your application around a relational database often has certain practices, basically, you have tables joined together with foreign keys, relations with lookups, and relations that represent master/detail records, or relations to other entities. And usually, a simple query has many table joins to get what you’re looking for.

On the other hand, switching to a NoSQL database may seem like you’re losing all of that. You don’t get an ORM, but probably you don’t need one. You don’t get table joins, which may appear very limiting at first. But if you think of common scenarios, you will find more benefits than limitations. It requires a new mindset to work with NoSQL.

Let’s take a common example, a shopping cart order. To design that in NoSQL you get a JSON object that looks like that:

{
  orderDate: '7/9/2023',
  orderNumber: '213',
  items: [
    {
      quantity: 1,
      product: 'Egg',
      price: 4
    },
    {
      quantity: 2,
      product: 'Bread',
      price: 2.5
    }
  ],
  netTotal: 6.5,
  taxes: 1.25,
  total: 7.75,
  currency: 'USD',
  store: '01-NY',
  customer: '......',
  shippingAddress: {
    address: '1234 Main St.',
    address2: 'APT 123',
    state: 'NY',
    zipCode: '12345'
  },
  paymentMethod: 'Cash',
  orderStatus: '02-Paid',
  deliveryStatus: '04-Shipped'
}

That’s one object/document/record in your database terms, that conveniently has all of the info that you need to fetch from your database in order to display or make an operation on that order.

Let’s think about the same in terms of SQL/Relational data, this is how one Order record will look like as coming from a single table:

OrderDate: '7/9/2023',
OrderNumber: '213',
NetTotal: 6.5,
Taxes: 1.25,
Total: 7.75,
CurrencyId: 1,
StoreId: 1,
CustomerId: 12415,
ShippingAddressId: 1234,
PaymentMethodId: 1,
OrderStatusId: 2,
DeliveryStatusId: 4

You see how this version of the Order is missing a lot of useful info that we most likely will need. For example, to get something resembling the NoSQL order we just saw, we will need to join the following tables together:

• Order
• OrderItem
• Currency
• Store
• Customer
• Address
• PaymentMethod
• OrderStatus
• DeliveryStatus

So instead of a simple get operation from a DocumentDB, you will need somehow a complex query with 9 tables to join. Some of them return multiple entities like OrderItem. which makes it require an ORM just to convert it to readable objects in memory. You may say a relational database can handle this without much performance fuss. But for a developer writing this query every time they need to remember which tables to join. A database view may be used to simplify reading and reuse that complex query with joins.

Now what about writing? To insert such an order object in the database, how many inserts do we need? Let’s think about it:

• 1 insert for the main Order
• 2 inserts or more for OrderDetail
• 1 insert for the Address in case it was a new address

Let’s talk about how to implement that in your code. If you are working with code separation and clean architecture, you typically create a repository for this order class. That repository will take an object of Order (or call it a graph with the order as its root), and the repository will have logic that traverses this graph, and do the inserts/update operations in the database. With a relational database, you need to make it all in one transaction, because if something fails you don’t want to end up with an order inserted without items or without other dependent records.

Compare that with how to insert that order in a DocumentDB, it’s just 1 document that goes as is in the database.

Let’s take a step back, as I’m not saying use NoSQL in all situations. But certainly thinking about it the right way, there are a lot of cases that are much easier to implement with NoSQL vs Relational, only if the right mindset and the right design practices are applied.

To recap, relational databases enjoy the following features:

• Table joins
• Referential Integrity
• Locking and Transactions
• ORM support

NoSQL databases also have their perks:

• Easier grouping of a graph of objects into one document
• Faster read and write operations
• Simpler development