NoSQL Databases: Architecture, Data Models, and Practical Use

Now that we understand why NoSQL emerged, we can compare its fundamental ideas with the relational model. This lesson examines how relational and NoSQL databases organize data, handle relationships, enforce consistency, execute queries, and scale. The goal is not to decide which model is better, but to understand the different assumptions behind each model and recognize which approach fits a particular workload.

The Relational Data Model

Relational databases organize data into tables. A table contains rows representing individual records and columns representing attributes of those records. For example, an airline system might have a Passengers table containing Id, Name, Email, and DateOfBirth. A separate Flights table could contain Id, FlightNumber, Departure, and Arrival.

Relationships between tables are represented using keys. For example, a Bookings table might contain PassengerId and FlightId, connecting a booking to a passenger and a flight. SQL databases are particularly strong at representing and querying these relationships.

SELECT p.Name, f.FlightNumber
FROM Bookings b
JOIN Passengers p ON b.PassengerId = p.Id
JOIN Flights f ON b.FlightId = f.Id;

The database engine performs the joins and returns the related information. This relational model is one of the main strengths of SQL Server and PostgreSQL.

Schemas and Schema Flexibility

A relational database normally has an explicit schema. Before inserting data, we define the structure of a table and the types of its columns.

CREATE TABLE Products (
    Id INT PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    Price DECIMAL(10,2) NOT NULL
);

This provides strong structure and validation. If another part of the application tries to insert a string into Price, the database can reject it. This is useful for systems where data consistency is important.

Document-oriented NoSQL databases such as MongoDB normally provide much more flexible schemas. Two documents in the same collection can contain different fields.

{
  "name": "Laptop",
  "price": 1200,
  "brand": "Dell"
}

Another product might contain additional information.

{
  "name": "T-Shirt",
  "price": 30,
  "size": "Large",
  "color": "Black"
}

This flexibility is useful when data structures change frequently or when different entities naturally have different attributes. However, schema flexibility does not mean "no schema." The application still has an implicit data model, and good systems often enforce rules through application validation or database-level schema validation.

Normalization vs. Denormalization

Relational database design commonly uses normalization to reduce duplication. For example, instead of storing a customer's name and email repeatedly inside every order, the database can store the customer once and reference that customer from the Orders table.

This reduces duplicated data and makes updates easier. If a customer changes their email address, we update one record rather than potentially thousands of orders.

NoSQL systems frequently use denormalization. Related information may intentionally be stored together because the application frequently needs that information in one operation.

A MongoDB order document might look like this:

{
  "orderId": 1001,
  "customer": {
    "name": "John Smith",
    "email": "john@example.com"
  },
  "items": [
    { "product": "Laptop", "quantity": 1 },
    { "product": "Mouse", "quantity": 2 }
  ]
}

The same information is duplicated compared with a normalized relational design, but reading an order can become much simpler and faster because the application does not need several queries and joins.

The architectural principle is important: relational modeling often starts from entities and relationships, while NoSQL modeling often starts from access patterns.

Joins vs. Application-Side Data Access

SQL databases are designed to perform joins efficiently. If an application needs customers together with their orders and payments, SQL can retrieve the related data using joins.

NoSQL databases generally discourage designing applications around large numbers of cross-document or cross-collection joins. Instead, data is often embedded, duplicated, or retrieved through multiple application-level operations.

For example, an application might retrieve a customer from one collection and then retrieve that customer's orders separately. This gives the application more responsibility for combining the data.

This approach can improve scalability for specific workloads, but it also introduces a trade-off. If your application constantly needs complex relationships between many entities, a relational database may be a more natural choice.

Transactions

Both relational and modern NoSQL databases can support transactions, but their capabilities and typical usage patterns differ.

SQL Server and PostgreSQL have mature support for multi-row and multi-table ACID transactions.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 1000
WHERE Id = 1;

UPDATE Accounts
SET Balance = Balance + 1000
WHERE Id = 2;

COMMIT;

The two operations can succeed together or fail together. This is essential for financial operations.

MongoDB also supports transactions across multiple documents and collections. However, a NoSQL system is often designed so that important operations can be completed atomically within a single document or aggregate, reducing the need for distributed transactions.

The architectural question should therefore not simply be, "Does this NoSQL database support transactions?" Instead, ask, "How much transactional coordination does my business operation actually require?"

Referential Integrity

Relational databases can enforce referential integrity using foreign keys. For example, a booking can reference a passenger:

FOREIGN KEY (PassengerId) REFERENCES Passengers(Id)

The database can prevent a booking from referencing a passenger that does not exist. It can also control what happens when the passenger is deleted.

Many NoSQL databases do not provide the same relational foreign-key model. The application is often responsible for maintaining relationships and ensuring that references remain valid.

This provides greater flexibility and can reduce coordination between distributed nodes, but it also moves more responsibility into the application architecture.

Query Patterns

Relational databases are designed to support flexible querying. SQL allows developers to filter, join, group, aggregate, sort, and analyze data in many different ways.

For example:

SELECT FlightNumber, COUNT(*) AS BookingCount
FROM Bookings
GROUP BY FlightNumber
ORDER BY BookingCount DESC;

This makes relational databases very useful when requirements involve unpredictable or complex queries.

NoSQL databases are generally more access-pattern-oriented. Before designing the data model, the architect should understand how the application will retrieve the data.

For example, if the main requirement is "retrieve all orders for a customer," the NoSQL partition or document structure can be designed specifically for that operation. This can produce excellent performance at scale, but it can make unexpected queries more difficult.

Scaling Models

SQL Server and PostgreSQL traditionally scale vertically by making the database server more powerful. They can also scale horizontally using technologies such as read replicas, partitioning, clustering, and sharding solutions.

Many NoSQL databases were designed from the beginning for horizontal scaling. Data can be distributed across multiple nodes using partitioning or sharding.

For example, a MongoDB cluster can distribute documents across multiple shards. Cassandra distributes data across nodes in a cluster. DynamoDB automatically distributes data based largely on partition keys.

This does not mean that NoSQL automatically scales better. It means that many NoSQL systems make horizontal distribution a central part of their architecture.

Consistency Models

Relational databases traditionally provide strong transactional consistency. After a successful transaction commits, subsequent operations normally observe the committed state according to the database's isolation and consistency guarantees.

Distributed NoSQL databases may provide several consistency options. For example, a system might prioritize availability and allow replicas to temporarily contain different versions of data before they converge.

Consider a social-media "like" counter. A temporary difference between replicas might be acceptable because displaying 10,001 likes instead of 10,000 for a short period is usually not a critical business problem.

For a bank balance, however, such temporary inconsistency could be unacceptable. The required consistency model should therefore be determined by the business requirement, not by database fashion.

Practical Comparison

Architectural Perspective

The difference between SQL and NoSQL is therefore not simply "tables versus JSON." The deeper difference is how each family approaches data modeling, relationships, transactions, querying, consistency, and distribution.

If you are designing an accounting system with complex relationships and strict transactions, PostgreSQL or SQL Server may be the natural choice. If you are building a globally distributed product catalog with flexible product attributes and very high read traffic, MongoDB or another NoSQL technology may be more appropriate.

As an architect, you should evaluate data structure, access patterns, transaction boundaries, consistency requirements, scalability, and operational complexity together. The correct question is not "SQL or NoSQL?" but "Which persistence model gives this workload the best trade-offs?"