Creating Relationships

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

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.

Sample Tables

Customer

SalesOrder

• 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.

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
• Indexing Best Practices
• Database Normalization (Wikipedia)