Mastering SQL with PostgreSQL

Backend Development | Mastering SQL with PostgreSQL

Explore

Schema Design and Transaction Management

RDBMS vs NoSQL Databases

Databases are essential for storing, managing, and retrieving data in a structured way. Databases can be classified into different categories depending on how the data is stored and managed. Broadly, they fall under Relational Databases (RDBMS) and Non-Relational Databases (collectively called NoSQL Databases), each serving different purposes.

Relational Databases (RDBMS)

SQL databases are Relational Database Management Systems (RDBMS). In these systems, data is stored in tables, which are made up of rows and columns. However, these databases are "relational" because different tables can be related to each other by sharing common data.

How data is stored

Tables with Rows and Columns: In an RDBMS, data is stored in tables that look like grids. Each row represents a record (like a user), and each column represents information about that record (like the user’s name, age or email).

Example (Users table):

Table 1

Table 1

UserID

Name

Age

Alice

alice@mail.com

Bob

bob@mail.com

There are no rows in this table

⁠

Relationships Between Tables

In an RDBMS, the real power comes from relating tables together. For example, you might have one table that stores information about users (like the one above) and another that stores information about the orders they’ve made. The relationship is that a user can place multiple orders.

Example (Orders table):

Table 2

Table 2

OrderID

UserID

Product

Price

1,001

Laptop

$1000

1,002

Smartphone

$500

1,003

Tablet

$300

There are no rows in this table

⁠

In this table:

The Users table contains information about people, while the Orders table contains purchase information.

Notice that the UserID column appears in both tables. This is how we know which user made which order. For example, Alice (UserID 1) bought a laptop and a smartphone, while Bob (UserID 2) purchased a tablet.

How relationships help

Because these tables are related, you can write SQL queries to pull information from both at once. For example:

"What products did Alice buy?"

If you looked for all the orders in the Orders table with UserID 1 (which corresponds to Alice), you would find that she bought a laptop and a smartphone.

Some Popular RDBMS Databases

PostgreSQL

MySQL

Microsoft SQL Server

Oracle Database

When to Use SQL Databases

Structured data: When your data can be clearly defined in tables, and you want to maintain clear relationships between that data (e.g., users and their purchases).

Relational data: When you need to retrieve related data across different tables in a consistent and structured way.

Data integrity: When ensuring that data adheres to strict schema (what fields and their data types for each row of the table), rules (e.g., no duplicate records, no orphaned records) are important

There are other advantages of RDBMS, which you’ll understand as we learn more.

NoSQL Databases (Non-Relational Databases)

NoSQL databases don't rely on tables and rows. Instead, they are designed to handle large volumes of unstructured or semi-structured data. NoSQL comes in many forms, each suited to different data types and use cases.

There are a lot of different types of NoSQL databases, but here are some of the most commonly used ones:

Types of NoSQL Databases

Key-Value Database

In this type, data is stored as simple key-value pairs, where each key is unique and points to a specific value. You can query the database by passing in a key, and you get the value associated with that key.

How data is stored

Key-Value Pairs: Each record consists of a unique key and a corresponding value. Think of it like a dictionary, where the key is a word, and the value is its definition.

⁠

When to Use Key-Value Databases

For applications requiring fast lookups (e.g., caching systems)

When you need to store simple data without complex relationships

Popular Key-Value Databases

Redis,

Amazon DynamoDB

Document Database

A Document Database stores data in a format like JSON or XML, which allows for flexible and hierarchical data storage.

How data is stored

Documents: Each record is called a "document," and different documents can have different structures, unlike the rigid structure of tables in SQL databases. This flexibility makes document stores ideal for situations where data may not always fit a strict schema.

Example (a document for a user in JSON format):

{

"userID": 1,

"name": "Alice",

"age": 25,

"address": {

"city": "New York",

"postalCode": "10001"

}

Another document might have different fields:

{

"userID": 2,

"name": "Bob",

"email": "bob@mail.com"

}

Relationship between Documents

While document stores don’t enforce relationships like SQL databases, you can create relationships between documents logically. For instance, one document might reference another document’s ID, establishing a link between them.

When to Use Document Databases

When your data is semi-structured or unstructured, and you need flexibility.

When you want to store complex data (e.g., JSON objects) without worrying about fitting everything into strict columns and rows.

For applications where data relationships are needed but not as strictly enforced as in SQL

Popular Document Databases

MongoDB

CouchDB

Wide-Column Databases

A Wide-Column Store is a type of NoSQL database that stores data in rows and columns but with much more flexibility than a traditional SQL database. Each row can have a different set of columns, which is why it's called "wide-column".

How data is stored

Rows and dynamic columns: In a wide-column store, each row can have its own set of columns, and not every row needs to have the same columns. This makes it highly flexible for large-scale distributed systems.

Example (users and their varying attributes):

Row 1: { "Name": "Alice", "Email": "alice@mail.com" }

Row 2: { "Name": "Bob", "Phone": "123-456-7890" }

In this example:

Alice’s row only has her name and email.

Bob’s row has his name and phone number but no email.

When to Use Wide-Column Databases

When you need to store large amounts of data across distributed systems.

When your data doesn't fit into a strict table structure, each row might have a different set of columns.

It is best for use cases like large-scale data analysis or real-time systems (e.g., IoT, big data applications).

Popular Wide-Column Databases

SyllaDB

Apache Cassandra

HBase

Graph Databases

A Graph database is designed to store relationships between data in a way that's easy to traverse. The data is stored as nodes (entities) and edges (relationships), making it perfect for understanding highly complex and connected data.

How data is stored

Nodes and Edges: Think of nodes as data points (like users) and edges as the relationships between them (e.g., friend connections).

⁠

When to Use Graph Databases

When relationships between data are essential (e.g., social networks, recommendation engines).

When you need to query and analyze complex networks.

Popular Graph Databases

Neo4j

Amazon Neptune

Summary: SQL vs. NoSQL Comparison

Table 3

Table 3

Column 1

Column 2

Column 3

Column 4

Database Type

Data Model

Use Case

Examples

SQL (RDBMS)

Tables with rows/columns

Structured data, strong relationships

MySQL, PostgreSQL

Key-Value (NoSQL)

Key-value pairs

Simple, fast lookups, caching

Redis, DynamoDB

Key-Value (NoSQL)

Documents (JSON, XML)

Flexible data, unstructured/semi-structured data

MongoDB, CouchDB

Wide-Column (NoSQL)

Rows with dynamic columns

Large-scale, distributed systems

SyllaDB, Cassandra, HBase

Graph (NoSQL)

Nodes and edges (relationships)

Complex relationship queries, networks

Neo4j, Amazon Neptune

There are no rows in this table

⁠

Conclusion

SQL databases offer structure and reliability, perfect for data with clear relationships. On the other hand, NoSQL databases provide flexibility, scalability, and performance, making them ideal for modern, large-scale applications. The ones covered here aren’t exhaustive, and there are many different types of NoSQL databases, each with its strengths, so it's important to choose based on your specific needs.

Understanding these basics will help you choose a suitable database for future projects. As you progress in this course, you'll dive deeper into SQL and learn to query relational databases efficiently. Stay curious, and feel free to revisit this guide as needed!

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.