---
title: "01 — Relational Foundations: Why SQL Databases Look Like They Do"
uid: relational-foundations
tags: ["sql", "roadmap:sql", "nosql", "relational-databases", "rdbms", "fundamentals"]
excerpt: "A relational database organizes data into typed tables linked by shared keys — and SQL is the language designed specifically to talk to that shape."
date: 2026-08-13T03:27:39+0000
source: https://www.aveshina.my.id/en/blog/relational-foundations
---

"Just background" was how I skipped past relational foundations before writing any query, and the skip cost me later. The idea that everything else hangs off: **a relational database organizes data into typed tables, and links those tables with shared key columns — and SQL is the language designed specifically to talk to that shape.** [1][2]

The framing that finally landed is the contrast. A database is just a place to store facts so I can get them back. There are many shapes such a store can take. The relational shape — tables of rows and columns, related by keys — is one specific, opinionated answer, and SQL was built to match it. Once I could see the shape, the commands stopped feeling arbitrary and started feeling like the only natural way to address a table.

```figure
<svg viewBox="0 0 700 280" xmlns="http://www.w3.org/2000/svg" class="my-6 w-full max-w-2xl" role="img" aria-label="Two related tables. Left table customers has columns id, name, email with rows 1 Ave, 2 Lin. Right table orders has columns id, customer_id, total. A dashed line connects customers.id to orders.customer_id, showing the relationship.">
  <g font-family="ui-sans-serif, system-ui, sans-serif" text-rendering="geometricPrecision">

    <!-- customers table -->
    <rect x="30" y="40" width="240" height="190" rx="8" fill="#e0e7ff" stroke="#6366f1" stroke-width="1.5"/>
    <text x="150" y="62" font-size="13" font-weight="700" fill="#1e1b4b" text-anchor="middle">customers</text>
    <line x1="30" y1="72" x2="270" y2="72" stroke="#6366f1" stroke-width="1"/>

    <text x="46" y="92" font-size="11" font-family="ui-monospace, monospace" fill="#1e1b4b" font-weight="700">id</text>
    <text x="96" y="92" font-size="11" font-family="ui-monospace, monospace" fill="#475569">1</text>
    <text x="146" y="92" font-size="11" font-family="ui-monospace, monospace" fill="#475569">Ave</text>
    <text x="206" y="92" font-size="10" font-family="ui-monospace, monospace" fill="#475569">ave@x.io</text>

    <text x="46" y="112" font-size="11" font-family="ui-monospace, monospace" fill="#1e1b4b" font-weight="700">id</text>
    <text x="96" y="112" font-size="11" font-family="ui-monospace, monospace" fill="#475569">2</text>
    <text x="146" y="112" font-size="11" font-family="ui-monospace, monospace" fill="#475569">Lin</text>
    <text x="206" y="112" font-size="10" font-family="ui-monospace, monospace" fill="#475569">lin@x.io</text>

    <text x="46" y="140" font-size="10" fill="#64748b" font-style="italic">column = attribute</text>
    <text x="46" y="156" font-size="10" fill="#64748b" font-style="italic">row = one record</text>

    <!-- orders table -->
    <rect x="430" y="40" width="240" height="190" rx="8" fill="#dcfce7" stroke="#16a34a" stroke-width="1.5"/>
    <text x="550" y="62" font-size="13" font-weight="700" fill="#052e16" text-anchor="middle">orders</text>
    <line x1="430" y1="72" x2="670" y2="72" stroke="#16a34a" stroke-width="1"/>

    <text x="446" y="92" font-size="10" font-family="ui-monospace, monospace" fill="#052e16" font-weight="700">customer_id</text>
    <text x="546" y="92" font-size="11" font-family="ui-monospace, monospace" fill="#475569">1</text>
    <text x="586" y="92" font-size="11" font-family="ui-monospace, monospace" fill="#475569">120</text>

    <text x="446" y="112" font-size="10" font-family="ui-monospace, monospace" fill="#052e16" font-weight="700">customer_id</text>
    <text x="546" y="112" font-size="11" font-family="ui-monospace, monospace" fill="#475569">1</text>
    <text x="586" y="112" font-size="11" font-family="ui-monospace, monospace" fill="#475569">45</text>

    <text x="446" y="140" font-size="10" fill="#64748b" font-style="italic">key column links</text>
    <text x="446" y="156" font-size="10" fill="#64748b" font-style="italic">back to customers</text>

    <!-- relationship line -->
    <path d="M150,170 C300,210 380,210 550,170" fill="none" stroke="#db2777" stroke-width="1.5" stroke-dasharray="5,4"/>
    <text x="350" y="226" font-size="10" fill="#500724" text-anchor="middle" font-style="italic">shared key = the relationship</text>
  </g>
</svg>
```

## What "relational" actually means

A relational database stores data in **tables** — grids of rows and columns [1]. Each table represents one kind of entity: a customers table, an orders table, a products table. Each **column** is an attribute with a fixed type (name, email, price), and each **row** is one concrete record. The word "relational" is the part I'd been underweighting: tables are linked to each other through **shared key columns**. orders.customer_id points back to customers.id, so I can ask "which orders did customer 1 place?" by following that link [2].

That's the whole shape. Everything in SQL — JOIN, foreign keys, normalization (splitting data across tables to avoid duplication), even the syntax of SELECT — is a consequence of tables being addressed by column and related by key.

## Why this shape won: the benefits

The relational model earned its dominance because of what the table-plus-key structure guarantees [3]:

- **Data integrity through constraints and keys.** A primary key makes every row uniquely addressable; a foreign key makes the database refuse an order whose customer_id doesn't exist. Bad data is rejected at the door.
- **ACID transactions.** Operations group into units that are atomic, consistent, isolated, and durable — so a transfer between two accounts either fully completes or fully rolls back, never half-applied.
- **A declarative query language.** I describe *what* data I want; the engine decides *how* to fetch it. I don't walk a tree or loop a cursor.

The trade-off is rigidity: the schema is fixed up front, every row in a table has the same columns, and changing the shape (adding a column) is a deliberate operation.

## Where the relational model strains: the limitations

The same rigidity that gives integrity is the limitation [3]:

- **Horizontal scaling is hard.** Splitting one logical database across many machines while keeping joins and transactions correct is genuinely difficult, so RDBMS usually scale up (bigger box) rather than out (more boxes).
- **Rigid schemas resist unstructured data.** When the data is documents, nested arrays, or fields that vary per record, forcing it into uniform columns feels like fighting the model.

That friction is exactly what motivated the NoSQL side of the conversation.

## SQL vs NoSQL: a question of shape

SQL and NoSQL aren't enemies — they're different answers to "what shape should the store be?" [4]

- **SQL (relational):** fixed tables and columns, strict schemas, joins across tables, strong consistency. Best when the data is structured, relationships matter, and correctness is non-negotiable.
- **NoSQL (non-relational):** flexible or nested documents, looser schemas, denormalized (data duplicated across machines) for scale. Best when the data shape varies, the volume outgrows one machine, or availability beats strict consistency.

The decision rides on the same few questions every time: *Is my data structured and related, or variable and nested? Do I need strong consistency or horizontal scale? Are complex joins central to the workload, or do I mostly fetch one document?* [4] Most production systems I've touched use SQL for the core transactional truth and reach for something NoSQL-shaped at the edges — caches, logs, search indexes — where the shape fits better.

## How I use this

The practical payoff is a sizing check before I write a single CREATE TABLE. I ask whether the data is structured and relationally linked (customers → orders → products), whether I need strong consistency for money-like writes, and whether the workload is join-heavy. If yes, the relational shape plus SQL is the default and I don't second-guess it. I reach for NoSQL when one of those flips — variable shape, massive horizontal scale, or document-style fetch patterns. Getting that call right up front saves the painful "we modeled it as documents and now we need joins" conversation later.

## References

[1] Amazon Web Services, "What is a relational database?," AWS, 2024. [Online]. Available: [https://aws.amazon.com/relational-database/](https://aws.amazon.com/relational-database/)

[2] Mode Analytics, "SQL Tutorial," mode.com, 2024. [Online]. Available: [https://mode.com/sql-tutorial/](https://mode.com/sql-tutorial/)

[3] Google Cloud, "What is a relational database? — Advantages and disadvantages of DBMS," Google Cloud Learn, 2024. [Online]. Available: [https://cloud.google.com/learn/what-is-a-relational-database](https://cloud.google.com/learn/what-is-a-relational-database)

[4] MongoDB, "Understanding SQL vs NoSQL databases," mongodb.com, 2024. [Online]. Available: [https://www.mongodb.com/resources/basics/databases/nosql-explained/nosql-vs-sql](https://www.mongodb.com/resources/basics/databases/nosql-explained/nosql-vs-sql)

```quiz
Q: In a relational database, how are two tables linked together?
- By storing copies of rows inside each other
- Through a shared key column, like orders.customer_id pointing to customers.id
correct: 1
explain: Relationships are expressed by key columns that one table references in another. A foreign key formalizes that pointer and lets the database enforce it.

Q: Which is a benefit of the relational model?
- Schemas are flexible and vary per row
- Data integrity through constraints and ACID transactions
correct: 1
explain: Fixed schemas, primary/foreign keys, and ACID transactions give the relational model its strong consistency and integrity guarantees.

Q: A common limitation of RDBMS is…
- they cannot store any structured data
- horizontal scaling across many machines is difficult
correct: 1
explain: RDBMS scale up well but struggle to scale out, because keeping joins and transactions consistent across sharded machines is hard.

Q: When is NoSQL typically a better fit than SQL?
- The data is variable/nested and horizontal scale matters more than strict consistency
- Every write must be a fully consistent, join-heavy transaction
correct: 0
explain: NoSQL fits variable, document-shaped data and horizontal scale. SQL fits structured, relationally linked data needing strong consistency.

Q: SQL is best described as…
- a declarative language for talking to the relational (table-and-key) shape
- a NoSQL document store
correct: 0
explain: SQL is the declarative query language designed for the relational model — I say what data I want, the engine decides how to fetch it.
```