Selecting Data from Multiple Tables

Finding Table Relationships

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

This video explains how to work with Table Relationships and how they affect joins.

Keywords

  • T-SQL
  • Tables
  • Relationships
  • Parent Tables
  • Child Tables
  • Primary Key
  • Foreign Key

About this video

Author(s)
John Deardurff
First online
05 April 2019
DOI
https://doi.org/10.1007/978-1-4842-4905-5_2
Online ISBN
978-1-4842-4905-5
Publisher
Apress
Copyright information
© John Deardurff 2019

Video Transcript

Hello, and welcome to this video segment on finding table relationships. We’ve been talking about different join types– left outer join, right outer join, and inner join. But to really understand the different join types, we need to take a step back and look at our database design to see how our relationships are used to connect our tables.

So here you can see the bookstore database with four tables– customers, orders, products, and employees. This is called an entity relational diagram. However, we’ve not included the relationships yet.

To add the relationships, we need to find the fields that can be used to connect the tables to each other. For example, the customers and orders table both have a customer ID field. So that would be an excellent field to connect those two tables with each other.

Same thing between employees and orders. There’s an employee ID field that can be used to connect those two tables, or to establish a relationship between those two tables. And then finally, product ID to product will allow us to establish the relationship between products and orders.

Now let’s look at our relational diagram here within our bookstore database. We’ll right-click on Database Diagrams and select a new database diagram. But here I’m going to go ahead and hold down my Control key to select the four tables for the bookstore database. I’ll click Add and then I’ll click Close, and as you can see, we have our four tables. I’m going to move these around a little bit to make it look very similar to the layout that we had on our previous slide.

So you can see that we do have the relationships between the several tables. Before we discuss the relationships in much more detail, let’s first talk about a term called primary key. Now a primary key within a table is a unique identifier. This is the column or field that uniquely identifies each record within that table. For example, in the customers table, the customer ID field is the primary key because it’s going to uniquely identify each of my customers. Same thing with the orders. The order ID field will be the unique identifier in that table. And then employee ID will be the unique identifier, or primary key, in the employees table, and product ID will be the primary key for the products table.

So in our relational diagram, you can see the primary keys for each of our tables, where they have a golden key next to the field name– so for the Customers table, Customer ID, for the Orders, the Order ID, Employee ID for Employees table, Product ID for the Products table. The reason I bring up the primary key here is because there’s also the primary key definition within a relationship. The primary key within a relationship is the best describer of the connecting field, or the field that’s connecting the two tables. For example, the Customers table has the Customer ID field that’s connecting to the Cust ID field in the Orders table to establish that relationship.

But which table best describes the field I’m connecting on? It would be the Customers table, because the Customers table tells me the first name and last name and where the customer actually lives– where in the Orders table, it only tells me the ID number of the person who placed that order. Same thing between Employees and Orders of connecting or establishing a relationship between the Employee table and the Orders table on the Employee ID field. Which table would best describe the Employee ID field? It would be the Employees table.

Once again, it’s telling me the first name, last name, and where that employee lives– where in the Orders table, it’s only telling me the ID of the person who sold the order. Finally, the Products table to Orders– I’m connecting on the Product ID field, but which table best describes the Product ID field. In this case, Products tells me the product name. Now, another definition we have is the foreign key. The foreign key is the other side of the relationship that connects my child table to my parent table.

Before we discuss the foreign key in more detail, let’s look at it within our SQL Server Management Studio database diagram. You can see our relationships. They’re connecting the Customers to the Orders table. If I right click on the relationship and select Properties, our property window opens up. And I select Table and Column Specifications. And here I could quickly see that my Customer ID field will be my primary unique key column for my primary table.

And then my foreign key column is Cust ID. And the foreign key base table would be Orders. If I click on Table and Column Specifications, I’ll get an ellipsis or three dots that I can click on. And I can also see the relationship between the Customers table and the Orders table, where my Customers table will have the primary key in the relationship and my Orders table will have the foreign key. In the relationship between Customers and Orders, if the Customer ID field in the Customers table is the primary key, then the Cust ID field in the Orders table would be my foreign key.

Between Employees and Orders, the employee ID field in the Employees tables is the primary key. And the Employee ID field in the Orders table would be the foreign key– the other side of the relationship. And once again, Products table– Product ID in the Products table would be the primary key, and the Product ID in the Orders table would be the foreign key. This is a key factor to know the difference between primary key and foreign key, because the side of the relationship that has the primary key will be the parent table. And the side of the relationship that has the foreign key would be the child table in that relationship.

This also establishes our left table and our right table. If we take a look at our joins, our parent table is always on the left and our child table is always on the right. So it is good to understand our relationships between our tables to understand our primary key and our foreign key to really understand our parent table to a child table, and our left and our right. We’ll discuss more about join types in the very next video segment. So in this video, we have discussed how to find table relationships and why they are so important with our different join types.