A Look Inside Relational Databases: Where Data Lives in Tables & Connects Through Keys
This article builds on the ideas introduced in Database Foundations, where we looked at why databases exist, how they organize information, and why structure matters. 👉 Database Foundations
Relational databases are one of the best examples of structured data — information that is predictable, organized, and stored in a clear pattern. Instead of random formats or flexible documents, a relational database stores data in tables made of rows and columns, where each column has a defined purpose and each row represents a single record.
Because everything is neatly organized, relational databases make it easy to search, filter, update, and analyze data with high accuracy.
What Makes a Relational Database “Relational”? 🗄️
A relational database follows a schema — a strict set of rules that defines what the data should look like. Every table, every column, and every type of data stored has a blueprint. All databases are managed by a Database Management System (DBMS).
Relational databases use a specialized version called an: RDBMS — Relational Database Management System
Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server
The RDBMS makes sure the database stays clean, consistent, and error-free by enforcing rules such as:
- 🚫 No duplicate rows
- 🚫 Column values should NOT be arrays or repeated groups
- ✔️ Missing data must be represented using NULL
Understanding NULL: One of the Most Misunderstood Concepts 🕳️
A NULL value means “data is not available” — not zero, not blank, not unknown… simply no value exists.
Key differences 🔍
- 0 means something (a number representing zero).
- NULL means nothing was provided — the data is missing.
- NULL ≠ 0
- NULL ≠ NULL. (Two missing values are not considered equal because we don’t know if they represent the same thing.)
A Real-Life Analogy
Imagine a form that asks: “How many cars do you own?”
- If someone writes 0, it means they explicitly own zero cars.
- If the field is left blank, we don’t know—maybe they forgot, maybe the question didn’t apply. That blank is NULL.
A Simple Scenario
A customer database stores:
- Age
- Phone
- Loyalty Points
If a customer never gave their phone number:
- Storing it as 0 would incorrectly imply: “Their phone number is 0.”
- Storing it as NULL correctly means: “We don’t have this information.
NULL helps the RDBMS differentiate between valid values and missing data, which is critical for filtering, sorting, and calculating accurately.
Schema — The Blueprint of a Relational Database 🏗️
Every relational database follows a well-defined schema. Think of the schema as the blueprint or master plan that describes:
- What tables exist
- What columns does each table contain
- What data types are allowed
- How tables are connected
- What forms, queries, and reports the system uses
In short, the schema is the backbone of every relational database and defines everything about how the database is organized.
Two Types of Schema: Logical & Physical 🏷️
Relational databases describe their design at two levels:
- Logical Schema — the high-level view of tables, fields, and relationships
- Physical Schema — how the data is actually stored on disk behind the scenes
Each of these will be discussed in detail in the sections below.
Logical Schema — the conceptual structure
This level answers the question: “What does the database contain and how do the tables connect?”. It includes:
- Table names
- Columns in each table
- Relationships between tables
- Primary & foreign keys
- High-level data rules
Logical schemas can be drawn using any diagramming tool—even a simple spreadsheet.
Logical Schema Example: Library Management System 📖
To make this easier for beginners, here’s a simple system with four tables .
Book Table
┌───────────┬──────────────┬───────────┬─────────┬───────────────┐
│ Book_ID │ Title │ Genre │ ISBN │ Author_ID │
└───────────┴──────────────┴───────────┴─────────┴───────────────┘
│
│
▼
Loan Table
┌────────────┬───────────┬──────────┬──────────────┬────────────┐
│ Loan_ID │ Book_ID │ Member_ID│ Loan_Date │ Return_Date│
└────────────┴───────────┴──────────┴──────────────┴────────────┘
▲ │
│ │
│ ▼
Member Table
┌───────────┬───────────┬──────────────┬──────────┬──────────────┐
│ Member_ID │ First_Name│ Last_Name │ Email │ Phone │
└───────────┴───────────┴──────────────┴──────────┴──────────────┘
▲
│
│
▼
Author Table
┌────────────┬──────────────┬──────────────┬──────────┬───────────┐
│ Author_ID │ First_Name │ Last_Name │ Country │ Email │
└────────────┴──────────────┴──────────────┴──────────┴───────────┘
Understanding the Relationships 🔗
This diagram shows how the four tables relate:
- Author → Book (1 to many)
- One author can write many books.
- Book → Loan (1 to many)
- The same book may be loaned multiple times to different members.
- Member → Loan (1 to many)
- A member may borrow multiple books over time.
These relationships tell the database how data should be connected, but they do not determine how data is stored physically. (That is what the physical schema will explain next.)
Why This Schema Matters for Data Access & Privacy 🔒
Because the logical schema defines exactly which tables connect, it also determines what information a user can access. Example: A member borrowing a book only needs:
- their Member_ID, and
- the Book_ID
Nothing in the schema connects one member to another member’s personal details — so the system naturally prevents a user from seeing someone else’s email or phone number. A simple way to explain this:
“A reader can check out a book, but they can’t open the drawer with all other readers’ personal information.”
The structure of the logical schema itself enforces data boundaries and maintains clean separation of information. The logical schema gives us the high-level view—tables, columns, keys, and relationships—without worrying about storage or indexing yet.
Physical Schema — The Real, Implemented Database 🧱
While the logical schema describes the conceptual design of the database, the physical schema is the actual implementation inside the RDBMS. It represents the real tables, columns, keys, and relationships created within the database software. In simple words:
- Logical schema = the plan
- Physical schema = the build
Most relational database tools can generate a physical schema automatically from the logical design. However, it can also be built manually, table by table and column by column.
Regardless of the approach, the logical schema should always come first. Planning your database on paper (or in diagrams) helps identify mistakes, missing relationships, or unnecessary data before anything is created in the system. Catching issues early makes the actual implementation much cleaner, safer, and easier to maintain.
Before diving deeper into the physical schema, it’s important to understand the key components that make up the actual implemented database. The topics below describe how the real tables, fields, and relationships are created and managed inside an RDBMS.
Tables — Where the Data Actually Lives 🧱
In the physical schema, the most essential building blocks are tables. Tables look very similar to worksheets in a spreadsheet — made up of rows and columns — and they store the actual data inside the database.
A relational database can (and usually does) contain multiple tables, each designed to store a specific type of information. For example, in a library system, you might have separate tables for Books, Members, Loans, and Authors.
- Each row in a table is called a record
- Each column represents a specific field of information
Linking Tables Together 🔗
To connect information across different tables, the data must be structured so that relationships are clear. This is usually done using identifiers (like a Customer_ID, Member_ID, or Book_ID).
Example:
A row in the Loan table uses both Member_ID and Book_ID to show which person borrowed which book. These identifiers make it easy for the database to match related records across tables.
Fields, Primary Keys & Foreign Keys (Physical Schema) 📌
Fields — The Columns Inside a Table 🧩
In a physical schema, the columns of a table are called fields. Each field stores one type of data for all records in that column. Examples of fields:
- Phone number
- Cost
- Zip code
- Quantity
- Date of Birth
A well-designed database never mixes unrelated data in the same field. For example:
- A field should not contain a part number for one record
- And a cost for another
These must be separate fields because when databases are queried, the user expects a consistent type of data from each field.
Primary Keys — Unique Identifiers for Records 🔑
Every table in a relational database must have a primary key. A primary key is one or more fields whose combined values uniquely identify each record. A primary key must follow these rules:
- 1️⃣ Uniqueness
No two records can have the same value in the primary key fields. Example: If a table used First_Name + Last_Name as a primary key:
- “John Smith” — allowed
- “Luke Thomas” — allowed
- Another “John Smith” — not allowed
- Another “Luke Thomas” — not allowed
(This is why names make poor primary keys.)
- 2️⃣ No NULL values
A primary key can never be blank. Every record must have a valid identifier.
- 3️⃣ Prefer a single field
Admins usually choose a field like:
- Customer_ID
- Member_ID
- Product_ID
- Employee_ID
These auto-generated IDs are simple, clear, and guaranteed to be unique.
- 4️⃣ Primary keys are automatically stored in an index
This index ensures uniqueness and makes record lookups extremely fast.
Foreign Keys — Connecting One Table to Another 🔗
A foreign key is one or more fields in a table that refer to the primary key of another table. Foreign keys help the database understand how records are related. Unlike primary keys:
- ✔️ Foreign keys can have duplicate values
- ✔️ Foreign keys can contain NULL
- ✔️ Multiple foreign keys can exist in one table
❓ Why can a foreign key be NULL if it references a primary key that cannot be NULL?
Because a foreign key does not always need to point to another record. Example: A book in the library database may not have an assigned author yet:
- Book_ID: 42
- Author_ID: NULL
This simply means “we don’t know the author yet.” NULL does not violate relational rules because:
- The foreign key exists
- But it is not currently referencing anything
- And that’s allowed
Only when a foreign key contains a value, it must match an existing primary key (unless constraints are disabled).
Foreign Key Constraints (Optional but Recommended)🗜️
Some RDBMS tools allow administrators to place constraints on foreign keys to prevent problems such as broken relationships. Constraints can prevent:
- ❌ 1. Entering a foreign key value that doesn’t exist
- Inserting a Loan with Book_ID = 999 when Book 999 does not exist.
- ❌ 2. Changing a foreign key to an invalid value
- Updating Loan.Book_ID to a book that doesn’t exist.
- ❌ 3. Deleting a primary key record that is still referenced
- This would create orphan records.
Example: Deleting a Member while their loans still exist → the Loan table would have meaningless references.
Foreign key constraints ensure database integrity.
NEW Physical Schema Diagram 📐
Using the Library Management System example (same one used in Logical Schema), here is a physical schema diagram.
Note on Key Symbols 🔑🗝️
In the diagrams used throughout this article, Primary Keys and Foreign Keys are shown using different key icons to make them easy to identify:
- 🔑 Primary Key — represents the field(s) that uniquely identify each record in a table.
- 🗝️ Foreign Key — represents the field(s) that link one table to another by referencing a primary key in a different table.
These symbols visually distinguish which fields uniquely define records and which fields create relationships between tables.
Book Table
🔑 Book_ID Title Genre ISBN ─────► 🗝️ Author_ID
│
│
▼
Loan Table
🔑 Loan_ID 🗝️ Book_ID 🗝️ Member_ID Loan_Date Return_Date
▲ │
│ │
│ ▼
Member Table
🔑 Member_ID First_Name Last_Name Email Phone
▲
│
│
▼
Author Table
🔑 Author_ID First_Name Last_Name Country Email
Primary Key vs Foreign Key (Comparison Table) 📊
| Detail | Primary Key | Foreign Key |
|---|---|---|
| Required? | Yes — every table must have one | No — tables can exist without foreign keys |
| # Allowed per table | Only one primary key per table | Any number of foreign keys allowed |
| Automatically Indexed? | Yes | Not always (depends on the system) |
| Duplicates Allowed? | No | Yes |
| NULL Allowed? | Never | Yes |
| Function | Uniquely identifies a record | Connects records across tables |
Indexes — Speeding Up Searches in a Database 📇
Just like the index at the back of a book helps you quickly find a topic without reading every page, database indexes help the DBMS find information faster without scanning the entire table.An index is a special data structure that stores pointers to the actual rows, allowing the database to jump directly to the data it needs.
Why Indexes Matter
Every table’s primary key automatically gets an index. This ensures quick lookups when searching for a specific record.
Additional indexes can be created on other columns that are searched frequently — such as Email, Member_ID, Book_Title, or Dates.
Indexes dramatically reduce the amount of data the database must scan, which can significantly speed up queries.
⚠️ But Indexes Have a Trade-Off
While indexes make reading data much faster, they can make updates, inserts, and deletes slightly slower—because the index itself must also be updated. For that reason, database designers carefully choose index columns, focusing on the fields most commonly used in searches, filters, and joins.
Real-Time Scenarios That Show How Indexes Work .
Searching for a customer in a huge Customers table. Imagine a bank with 2 million customers. If a support agent searches for:
“Find customer where Email = john@example.com”
Without an index, the database scans every single row until it finds the match. With an index on Email, the DB jumps directly to that record — like flipping to the “J” section of a phonebook. This makes a search that takes seconds finish in milliseconds.
Forms — A Simple Way to Enter and View Data 📝
Forms are the screens or interfaces that people use to enter, edit, or view data in a database. They sit on top of the database tables and make interaction much easier.
✔️ You don’t need forms to have a database
A database can exist with just tables. But forms make using the database much more user-friendly — especially for non-technical people.
Think of a form as a clean, guided input screen that shows only what the user needs to enter. Instead of typing data directly into a table (which looks like a spreadsheet), a form:
- Shows one record at a time
- Has clear labels for each field
- May include dropdowns, date pickers, or checkboxes
- Prevents users from entering invalid or unrelated information
- Can hide sensitive fields from users who shouldn’t see them
This protects the database from mistakes and keeps everything consistent.
🌟 Real Time Example
Imagine a Library Management System like the example above. A librarian checks out a book for a member.
Without forms: They would have to open the Loans table and manually enter:
- Loan_ID
- Book_ID
- Member_ID
- Loan_Date
- Return_Date
This requires:
- Knowing the exact Book_ID
- Knowing the exact Member_ID
- Not mistyping anything
- Not forgetting any fields
It’s easy to make mistakes.
With a form, the librarian just sees a simple screen: Checkout Book Form
- Select Member (drop-down list)
- Select Book (search box or drop-down)
- Loan Date (auto-filled)
- Return Date (calendar picker)
The form automatically fills the Loan_ID, validates the inputs, and sends clean data to the Loans table. It’s safer, faster, and more user-friendly.
Queries — The Real Power Behind Databases 🔍
Queries are what make a database truly useful. They allow you to search, filter, and analyze your data to find exactly what you need — even if your database contains millions of records.
Without queries, a database is just a giant collection of tables. With queries, it becomes a powerful information-finding engine.
What Queries Actually Do 🧠
A query lets you:
- Pull specific data from one or more tables
- Apply filters (e.g., “find all books borrowed after July 1”)
- Sort results
- Combine data from multiple related tables
- Generate reports
- Answer business questions instantly
In simple terms, A query is a question you ask your database, and the database returns the exact answer.
Beginner-Friendly Example 📘
Using the Library Management System example:
❓ Question: “Which books are currently checked out?”
✔️ Query result gives you:
- Book title
- Member who borrowed it
- Loan date
- Due date
This would be nearly impossible to find manually by scrolling through thousands of rows across multiple tables.
Why Queries Matter So Much
As your data grows, scrolling becomes unrealistic. Queries allow you to:
- Find needles in huge haystacks
- Avoid scanning tables manually
- Combine information across tables
- Get instantly actionable insights
This is why queries are considered the heart of relational databases — they turn raw data into meaningful information.
Reports — Ready-Made Answers From Your Data 📊
Reports are structured outputs that answer specific questions using data stored in your database. Think of them as prebuilt queries that present information in a clear, organized format.
A query retrieves the raw data. A report organizes that data into something meaningful and easy to read — often with totals, summaries, and formatted layouts.
Example
Using your Library Management System: ❓ Question: “How many books were borrowed this month, and who borrowed them?”
A report can show:
- Member names
- Book titles
- Loan dates
- Return dates
- Total number of loans this month
All nicely formatted — maybe grouped by member or sorted by date.
Why Reports Are Helpful 🧩
- They save time — no need to write the same query again
- They give consistent, standardized answers
- They can be scheduled (daily, weekly, monthly)
- They’re easy to share with managers, departments, or users
If queries are the questions, reports are the ready-made answers.
Macros & Modules — Adding Functionality to Your Database ⚙️
Macros and modules are tools that let you add extra functionality, automation, and custom behavior to your database. They sit on top of your tables, forms, and queries, and help the database do things automatically or perform tasks that go beyond basic data storage.
Macros — Automated Actions 🧩
A macro is a simple, predefined action that runs automatically when triggered. Think of macros as shortcuts or mini-robots inside your database.
Example, In the Library Management System above:
- When a book is checked out, a macro could automatically fill the Loan_Date with today’s date.
- Another macro could show a message if a book is already checked out before allowing a new loan.
- A macro could automatically open a specific form when the database starts.
Macros help non-technical users automate everyday tasks without writing full code.
Modules — Custom Code for Advanced Behavior 🧩🧩
A module is a piece of code (often written in a scripting language like VBA in systems such as MS Access) that lets you create more complex logic than macros can handle. Modules are for situations where you need precise control, calculations, or custom operations.
✔️ Example, Using the same library scenario:
- A module could calculate late fees based on the number of days a book is overdue.
- It could automatically send an email notification to members with overdue books.
- It could validate data in a more advanced way (e.g., checking multiple conditions before saving a record).
Modules are essentially programming inside your database.
How They Work Together 🧵
- Macros = simple automation, easy to set up
- Modules = more powerful logic using code
Both allow you to extend the database beyond simple data storage and retrieval.
Wrapping Up 🧭
Relational databases rely on structure, rules, and relationships to keep data organized, accurate, and easy to work with. From tables and keys to schemas, queries, and reports, each part works together to ensure information is stored consistently and retrieved efficiently.
You now have a clear picture of how RDBMS systems organize data and why they remain the backbone of many applications today.
In the next article, we’ll shift from structured systems to a more flexible world — Non-Relational (NoSQL) Databases, where rigid tables are replaced with document stores, key-value pairs, and horizontally scalable designs. 👉Non-Relational Databases
This article is part of the Database Fundamentals Series — where we break down what databases are, how they store information, and how data is organized, accessed, and used behind the scenes. 👉 — Database Fundamentals: Foundation of Data Storage