Last Updated on July 24, 2025

Before we dive into the topic of relationships between tables in a relational database, it’s worth revisiting the key concepts we explored in our previous post: Introduction to Relational Databases and SQL. In that article, we took a hands-on and detailed look at how relational database systems work and how we use SQL to create, manipulate, and query data.

Relational databases are powerful because they don’t just store data; they connect it in meaningful ways. These connections (or relationships) let us model the real world, ensure data integrity, and write queries that are both powerful and efficient.

In this post, we’ll explore why relationships matter, how they’re structured in a relational database, and how to work with them in Ruby on Rails using Active Record. Whether you’re new to backend development or brushing up your skills, understanding relationships will take your database design and your Rails apps to the next level.

What is the importance of relationships in relational databases?

Imagine an e-commerce system where you have Customers, Products, and Orders. If this information were stored without any connections between them, how would you know which orders belong to which customer?, or which products were purchased in a given order?

This is where relationships come into play. They’re the glue that connects different parts of your database and makes the data truly useful.

1. Data Organization and Consistency

Relationships help you model the real world within the database in a logical and organized way. For example:

• A customer can place multiple orders (one-to-many relationship).
• An order can contain many products, and each product can be in many orders (many-to-many relationship).

Without these explicit connections, you risk having duplicated, inconsistent, and hard-to-maintain data.

2. Referential Integrity

One of the biggest benefits of relational databases is the ability to ensure data integrity. For example, with a foreign key, the database can prevent you from creating an order for a customer who doesn’t exist, or deleting a customer who still has active orders.

This protects your data from common errors and avoids orphaned records that no longer make sense in the system.

3. Efficient Queries

With relationships properly defined, you can use joins to combine data from multiple tables quickly and efficiently.

This allows you to answer complex questions, such as “Which products did customer John buy last month?”; or “What was the best-selling product last month?”. Something impossible or costly if the data is disconnected.

4. Scalability and Model Maintenance

Well-designed relationships make your system more scalable and easier to maintain. When new features are added, it’s easier to integrate new entities into the existing model without creating a mess.

5. Promotes Development Best Practices

Understanding and using relationships correctly teaches best practices in data modeling, something every developer will use throughout their career, whether building APIs, designing applications using an Object-Relational Mapping (ORM), or optimizing a legacy system.

Understanding Relationships Between Tables

Now that we’ve seen why relationships are important, let’s take a step back and understand what they really are.

At its core, a relationship between tables defines how records in one table are connected to records in another. Think of it like connecting dots: each table represents a type of data (like customers, products, or orders), and the relationship defines how those dots relate to each other in a meaningful way.

In the database, this relationship is modeled by connecting the two tables, typically by having a column in one table that references the primary key of another.r.

Let’s go back to our e-commerce example:

• You have a list of customers.
• You have a list of orders.
• You have a list of products.

Each customer can place multiple orders. Each order can contain multiple products, and each product can appear in many different orders. These connections, or relationships, allow the system to answer questions like:

• “Which customer placed this order?”
• “What products were included in that order?”
• “How many times has this product been purchased?”

Without these links, your data would just be isolated pieces — useful on their own, but nearly impossible to use together.

Keys: The Foundation of Relationships

Keys are the fundamental elements that support relationships between tables and ensure data is organized, consistent, and easy to access.

A Primary Key (PK) is like a record’s identification number. Its main job is to ensure that every row in a table is unique and easily identifiable.

For example, in our Customers table, we have the id as the primary key. That way, each customer will have a unique id, and we can distinguish two customers even if they share the same name.

A Foreign Key (FK) is what allows tables to “talk” to each other. It creates a link between a column in one table and the primary key in another.

In our e-commerce database, the Orders table has a customer_id column that acts as a foreign key referencing the id in the Customers table. This establishes a relationship between the Orders and Customers tables, allowing you to, for instance, retrieve all orders placed by a specific customer in a clean and structured way.

Referential Integrity: Protecting Your Data

One of the biggest advantages of foreign keys is that they help enforce referential integrity. Simply put, this means:

• You can’t insert a foreign key value that doesn’t exist in the referenced table.
• If you try to delete a record that’s being referenced elsewhere, the database can block the action or handle it according to a defined rule (like ON DELETE CASCADE or ON DELETE SET NULL).

Referential integrity is key to avoiding issues like:

• Orders linked to customers who no longer exist.
• Orphaned records that lead to data inconsistency and confusion.

Enforcing referential integrity is one of the best practices in database design. It not only ensures data reliability but also makes your systems easier to maintain and scale over time.

How It Works in a Database

Here’s how these relationships are represented in a relational database:

CREATE TABLE customers(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR,
    last_name VARCHAR,
    active BOOLEAN
);

CREATE TABLE orders(
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    ordered_at TIMESTAMP
);

CREATE TABLE products(
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    description TEXT
);

CREATE TABLE order_products(
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER
);

The orders table includes a column customer_id that references the id of the customer who placed the order and this creates a one-to-many relationship.

The order_products table acts as a bridge between orders and products, allowing each order to contain multiple products, and each product to be in multiple orders: a many-to-many relationship.

This schema allows us to:

• Track which customer made which order.
• Link each order to the products it includes.
• Avoid redundant data by keeping products and orders in separate tables.

By defining relationships like this, your database becomes:

• Smarter, because it understands how data is structured.
• Safer, by preventing invalid or inconsistent data.
• Powerful, because you can query and combine data from multiple tables.

Without relationships, you’d have to rely on complex logic in your application layer or store redundant data, both leading to higher maintenance and more bugs.

Applying Relationships in Ruby on Rails with Active Record

Now that we understand the theory behind relationships in relational databases, let’s see how to put that into practice using Ruby on Rails : a popular web framework that makes working with databases a breeze through Active Record, its built-in ORM (Object-Relational Mapping) layer.

With Active Record, relationships between tables are represented as associations between models. This not only allows you to define how your data connects but also gives you powerful tools to query and manipulate related data.

Defining Relationships in Models

Let’s go back to our e-commerce example. Here’s how you’d model the relationships in Rails:

# app/models/customer.rb
class Customer < ApplicationRecord
  has_many :orders
end

# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :customer
  has_many :order_products
  has_many :products, through: :order_products
end

# app/models/product.rb
class Product < ApplicationRecord
  has_many :order_products
  has_many :orders, through: :order_products
end

# app/models/order_product.rb
class OrderProduct < ApplicationRecord
  belongs_to :order
  belongs_to :product
end

These associations directly map to the foreign key relationships defined in the database schema. Rails takes care of the heavy lifting, letting you navigate relationships with simple method calls.

Querying Data with Active Record

Once your models are set up, Active Record gives you several methods to query and interact with your data. Let’s explore some of the most useful methods.

.all

Returns all records from the table.

# returns all customers
Customer.all

.find

Finds a record by its primary key (id). Raises an error if the record isn’t found.

# returns the order with ID 3
Order.find(3)

.find_by

Finds the first record that matches a condition. Returns nil if nothing is found.

# returns the first customer with that email
Customer.find_by(email: "john@example.com")

.where

Returns a collection of records matching specific conditions.

# returns all orders placed by customer with ID 1
Order.where(customer_id: 1)

You can chain .where with other methods:

Order.where(customer_id: 1).where("ordered_at > ?", 1.month.ago)

.order

Sorts the result by one or more fields.

# returns all products sorted alphabetically
Product.order(:name)

# returns the most recent orders first
Order.order(ordered_at: :desc)

.limit

Limits the number of returned records — useful for pagination or previews.

# returns the first 5 products
Product.limit(5)

.or

Combines two where clauses with an OR condition.

# returns customers who are active OR were created in the last week
Customer.where(active: true).or(Customer.where("created_at > ?", 1.week.ago))

.find_each

Efficiently loads records in batches and yields them one at a time. Great for large datasets.

Customer.find_each do |customer|
  # do something with customer
end

You can use .find_each after .where to filter the data you want to process.

Customer.where(active: false).find_each do |customer|
  # do something with customer
end

.find_in_batches

Loads records in batches, yielding the batch as an array. Useful for bulk processing.

Order.find_in_batches(batch_size: 100) do |orders|
  orders.each { |order| puts order.id }
end

Querying Associated Data

In real-world Rails apps, you’ll often need to query across relationships, like finding all orders from active customers, or listing products in recent orders. While Active Record associations make accessing related records easy, querying them efficiently is just as important.

That’s where methods like .joins, .left_outer_joins, .includes, and .preload come in. They give you control over how Rails interacts with the database.

Let’s break them down with examples from our e-commerce app.

.joins

Use .joins when you need to filter or sort based on associated records. It performs an SQL INNER JOIN, meaning only records with matching associations are returned.

# returns orders placed by active customers
Order.joins(:customer).where(customers: { active: true })

orders.each do |order|
  puts order.customer.name  # will trigger an extra query for each order (N+1)
end

Note: .joins does not load the associated data into memory. Accessing order.customer here would trigger an extra query, possibly causing N+1.

.left_joins or .left_outer_joins

Use .left_outer_joins when you want to include records even if they don’t have related entries. It performs an SQL LEFT OUTER JOIN.

# returns all customers, including those with no orders
Customer.left_joins(:orders).where(orders: { id: nil })

.includes

Use .includes to eager load associations and prevent N+1 queries. Unlike the .joins method, .includes will load the associated data into memory. This is useful when you need to read associated data.

# returns all orders with their associated customers
orders = Order.includes(:customer)

orders.each do |order|
  puts order.customer.name  # won't trigger extra queries
end

.preload

.preload works like .includes but always uses separate SQL queries instead of trying to combine with joins. Use it when you want to avoid complex joins or when you know multiple queries will be more efficient.

# returns products and their associated orders in separate queries
Product.preload(:orders).each do |product|
  product.orders.each { |order| puts order.id }
end

Conclusion

Relationships are the heart of relational databases, and once you understand how they work, everything else starts to fall into place. They help keep your data consistent, your queries efficient, and your application logic clean and maintainable.

In Rails, Active Record gives you all the tools you need to define, navigate, and query these relationships with ease. From simple lookups to powerful associations and joins, mastering these tools will make you a better developer and help you build apps that scale.

Previously: Creating REST APIs with Ruby on Rails

This post is part of our ‘The Miners’ Guide to Code Crafting’ series, designed to help aspiring developers learn and grow. Stay tuned for more and continue your coding journey with us!! Check out the full summary here!

We want to work with you. Check out our Services page!