> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Creating Relationships
> This article explores defining and leveraging relationships between tables in structured data to model real-world business transactions.
Welcome back to our series on structured data. In this lesson, we’ll explore how to define and leverage relationships between tables to model real-world business transactions.
When you normalize your database, you often end up with one table per entity—**Customer**, **Product**, **SalesOrder**, and so on. Yet, a single transaction typically spans multiple tables. For example, to display the details of a given sales order, you must know:
* Which customer placed the order
* Which products were purchased
To connect these entities, we use relationships.
## Why Relationships Matter
Relationships let you:
* Maintain data integrity
* Avoid duplication by referencing shared data
* Query across entities using `JOIN` operations
Refer to [Database Normalization](https://en.wikipedia.org/wiki/Database_normalization) for best practices on organizing tables.
## Primary Keys vs. Foreign Keys
Each table defines a **primary key**, a column (or set of columns) that uniquely identifies each row. A **foreign key** is simply a copy of a primary key added to another table to create a link.
| Key Type | Purpose | Example |
| ----------- | ------------------------------------------------------- | ------------ |
| Primary Key | Unique identifier for a table’s rows | `CustomerID` |
| Foreign Key | References a primary key in another table for integrity | `CustomerID` |
### Sample Tables
**Customer**
| CustomerID | Name |
| ---------- | ----------- |
| 0001 | Alice Smith |
| 0002 | Peter Vogel |
| 0003 | Linda Jones |
**SalesOrder**
| OrderID | CustomerID | ProductID |
| ------- | ---------- | --------- |
| A123 | 0002 | P456 |
| A124 | 0001 | P789 |
* In **Customer**, `CustomerID` is the primary key.
* In **SalesOrder**, `CustomerID` and `ProductID` are foreign keys referencing the **Customer** and **Product** tables, respectively.
### Querying Across Tables
To find who placed order `A123`:
1. Query **SalesOrder** for `OrderID = 'A123'`.
2. Retrieve `CustomerID = 0002`.
3. Look up `0002` in **Customer** to get **Peter Vogel**.
## Performance Considerations
As your database grows, frequent `JOIN` operations can impact query response times.
Excessive joins on large tables without proper indexing can lead to slow queries. Always index foreign key columns and consider query optimization techniques like denormalization or materialized views when necessary.
In the next lesson, we’ll explore strategies—such as indexing, query planning, and denormalization—to optimize relationship performance in large datasets.
## Links and References
* [SQL JOINs](https://dev.mysql.com/doc/refman/8.0/en/join.html)
* [Indexing Best Practices](https://www.postgresql.org/docs/current/indexes.html)
* [Database Normalization (Wikipedia)](https://en.wikipedia.org/wiki/Database_normalization)