DEV Community

Nithya Sri D
Nithya Sri D

Posted on

Your Data Is Everywhere. Here's Why Relational Databases Exist.

SQL Beyond Syntax | Episode 01: Understanding Relational Databases and RDBMS

Imagine opening an e-commerce application.

You browse products, add something to your cart, place an order, and receive a confirmation. Everything happens within seconds.

But behind that simple experience, something interesting is happening.

The application needs to remember who you are, which products exist, what you ordered, how much you paid, and whether your order has been delivered.

Now imagine thousands of customers placing orders every day.

Where does all that information go? How does the application retrieve the correct order for the correct customer? And how does it avoid storing the same customer information unnecessarily across thousands of records?

These questions bring us to one of the most important foundations of modern software and Data Engineering:

Relational databases.

Welcome to Episode 1 of SQL Beyond Syntax, a series where we'll learn SQL through real-world problems rather than memorizing commands.

Today, we're starting before the first SQL query.

Because understanding why databases exist makes learning SQL much easier.


1. The Problem: What If We Stored Everything in One Table?

Suppose we're building a small e-commerce application.

Our first idea might be to store all the information in a single table.

Order ID Customer Email Product Price
101 Emma Wilson emma@example.com Laptop 500
102 James Anderson james@example.com Mouse 20
103 Emma Wilson emma@example.com Keyboard 35
104 Emma Wilson emma@example.com Monitor 200

All prices are illustrative and expressed in USD.

At first glance, this looks perfectly reasonable.

We can see the customers, products, and orders. Everything is organized into rows and columns.

But look at Emma's information.

Her name and email address appear three times.

What happens if she changes her email address?

We might need to update multiple records. If one record is missed, the database could contain conflicting information about the same customer.

This is called an update anomaly.

There are other potential problems too.

If we want to register a customer who hasn't placed an order, our table design might make that difficult. If we delete a customer's only order, we might accidentally lose the only stored copy of their information.

These are examples of insertion and deletion anomalies.

The problem isn't that tables are bad.

The problem is that we've combined different types of information into one structure without considering how they relate.

That's where relational database design becomes useful.

2. The Solution: Separate Information, Connect It Through Relationships

Instead of storing everything together, we can divide the information into logical tables.

Let's create three conceptual tables.

Customers

customer_id customer_name email
1 Emma Wilson emma@example.com
2 James Anderson james@example.com
3 Sophia Martinez sophia@example.com

Products

product_id product_name price_usd
201 Laptop 500
202 Mouse 20
203 Keyboard 35
204 Monitor 200

Orders

order_id customer_id product_id
101 1 201
102 2 202
103 1 203
104 1 204

Notice something important.

The Orders table doesn't repeat the customer's name or email.

Instead, it stores a customer_id.

Similarly, it stores a product_id rather than repeating the product's descriptive information.

These identifiers allow us to connect information across tables.

If Emma changes her email address, we can update her record in the Customers table without modifying every order.

This design helps reduce unnecessary duplication and improves data consistency.

It also introduces the central idea behind relational databases:

Information can be stored in separate tables while relationships connect it into a meaningful whole.

One important detail: this simplified example assumes each order contains one product. Real e-commerce databases often use an additional order_items table so one order can contain multiple products.

3. What Exactly Is a Relational Database?

A relational database organizes data into tables, also called relations.

Each table represents a collection of related information.

For example, a Customers table contains customer records, while a Products table contains product records.

A table has two fundamental components.

Rows: Individual records within the table.

Columns: Attributes that describe those records.

Consider this example:

customer_id customer_name city
1 Emma Wilson New York
2 James Anderson London
3 Sophia Martinez Toronto

Here, the table contains three rows and three columns.

Each row represents a customer. Each column represents an attribute of that customer.

But relational databases offer something more powerful than organizing information into tables.

They allow us to define relationships between those tables and retrieve connected information using SQL.

That is what makes them so useful for applications and analytical systems.

4. Primary Keys: How Does a Database Identify a Record?

Imagine two customers have the same name.

If we use names to identify customers, how do we distinguish them?

This is why databases use primary keys.

A primary key is a column, or combination of columns, that uniquely identifies each row in a table.

In our Customers table, customer_id can serve as the primary key.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(255),
    city VARCHAR(100)
);
Enter fullscreen mode Exit fullscreen mode

Let's understand this gradually.

CREATE TABLE tells PostgreSQL to create a new table.

customers is the table name.

INTEGER specifies a whole-number data type.

VARCHAR(100) allows a variable-length string of up to 100 characters.

PRIMARY KEY ensures that customer_id values are unique and cannot be NULL.

For now, don't worry about memorizing the entire statement. We'll explore table creation and data types in detail in upcoming lessons.

The important idea is simple:

A primary key gives each record a reliable identity.

5. Foreign Keys: How Do Tables Communicate?

Now let's return to our Orders table.

It contains a customer_id, but the actual customer details are stored in Customers.

How does PostgreSQL know that these identifiers are related?

Through a foreign key.

A foreign key is a column, or combination of columns, that references a key in another table.

Here's a simplified example:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,

    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);
Enter fullscreen mode Exit fullscreen mode

The foreign key connects orders.customer_id to customers.customer_id.

It helps enforce referential integrity.

For example, if we attempt to insert an order with a non-NULL customer_id that doesn't exist in Customers, PostgreSQL rejects that reference.

In this example, product_id is not yet defined as a foreign key, and customer_id is nullable. We'll strengthen these constraints when we study database design and DDL.

The key takeaway is that foreign keys help maintain valid relationships between records.

6. What Is an RDBMS?

We've discussed relational databases, but another term frequently appears alongside them:

RDBMS: Relational Database Management System.

A relational database is the organized collection of data.

An RDBMS is the software that manages relational databases.

It provides capabilities for storing, retrieving, updating, protecting, and managing data.

Popular examples include:

  • PostgreSQL
  • MySQL
  • SQLite
  • Microsoft SQL Server
  • Oracle Database

For this series, we'll use PostgreSQL.

Why PostgreSQL?

Because it supports relational modeling, SQL queries, transactions, indexes, constraints, and advanced analytical features.

It is useful for learning both application development and Data Engineering concepts.

7. Where Does SQL Fit Into This?

So far, we've organized information into tables and established relationships.

But how do we retrieve information from those tables?

That's where SQL comes in.

SQL stands for Structured Query Language.

It is the language used to interact with relational database systems.

Suppose we want to retrieve all customers.

SELECT *
FROM customers;
Enter fullscreen mode Exit fullscreen mode

Here:

SELECT specifies what information to retrieve.

* means all columns.

FROM identifies the table.

Now suppose we want customers from New York.

SELECT customer_id, customer_name
FROM customers
WHERE city = 'New York';
Enter fullscreen mode Exit fullscreen mode

Assuming the Customers table contains the sample records shown earlier, the expected output would be:

customer_id customer_name
1 Emma Wilson

Notice that we're describing what data we want rather than manually writing instructions to search every record.

That is one of SQL's most useful characteristics.

It lets us express questions about data in a structured, readable way.

8. Why Relational Databases Matter in Data Engineering

Until now, our examples have focused on a small e-commerce application.

But relational databases also play an important role in Data Engineering.

Imagine building an ETL pipeline.

Data arrives from APIs, files, or other systems. The pipeline extracts information, validates records, transforms values, and loads the results into a database.

Once stored, SQL can help answer business questions.

For example:

Which products generate the most revenue?

Which customers place the most orders?

How do sales change over time?

Which shipments experience unusually long delivery intervals?

These questions become easier to investigate when data is organized consistently and relationships are clearly defined.

In my ShopFlow project, I explored this idea by loading transformed e-commerce data into PostgreSQL using a star schema with one fact table and three dimension tables.

In CargoPulse, I worked with shipment events and used SQL joins, CTEs, and window functions to investigate logistics-related questions.

Both projects reinforced something important:

Good SQL queries depend on understanding the data model underneath them.

Writing SQL is only part of the skill.

Understanding what the tables represent, how records relate, and which assumptions the data model makes is equally important.

9. Relational Databases Have Limitations Too

Relational databases are powerful, but they aren't automatically the best choice for every problem.

A relational model works especially well when data has a clear structure, relationships matter, and consistency is important.

However, designing schemas and maintaining relationships requires planning.

Highly variable data structures can be less convenient to represent in traditional relational tables. Some workloads also require additional architecture to scale efficiently across distributed systems.

This doesn't make relational databases unsuitable for modern applications.

It simply means database selection should depend on the nature of the data and the requirements of the system.

We'll explore these trade-offs further when we compare SQL and NoSQL databases in a future episode.

10. Let's Practice: Think Like a Database Designer

Imagine you're building a university course registration system.

You need to store information about students, courses, and which students have enrolled in which courses.

Instead of putting everything into one table, consider the following questions.

Challenge 1: What three tables would you create to represent students, courses, and enrollments?

Challenge 2: Which columns would you choose as primary keys?

Challenge 3: Which foreign keys would connect the enrollment records to students and courses?

Bonus challenge: Why might an enrollment table be necessary instead of simply storing a course name in the Students table?

Try answering these questions before looking up a solution.

The goal isn't to memorize database terminology.

It's to begin thinking about how information should be organized.

11. What We Learned Today

In this first episode, we explored the reasoning behind relational databases rather than jumping directly into SQL commands.

We learned that storing unrelated information together can introduce duplication and consistency problems.

We saw how separating information into logical tables can improve organization.

We introduced primary keys, foreign keys, and referential integrity.

We also discussed the role of an RDBMS and how SQL allows us to retrieve information from relational databases.

Most importantly, we connected these ideas to real Data Engineering scenarios.

Before writing complex queries, we need to understand what our data represents.

Because SQL isn't just about retrieving rows. It's about asking meaningful questions of well-structured data.


Top comments (0)