So one great way to find related tables is to create a database diagram
I'm going to open up the AdventureWorks database and then right click on database diagrams and say
new database diagram. This will open a pane here and I can select what I'll call the central table
and what this will then do is find all the tables that are related to that central table
So I'm going to pick employee as my central table and hit add. You can see here nothing remarkable has happened yet. It just added the employee table to our
diagram. However, now if I right click and say add related tables, what it does is it goes out
and finds all the tables that are related to the employee table. You can see it's been going and
filling in. They're all kind of stacked up and hard to read so I can right click again and then
say arrange tables. So now you can see where we have our diagram laid out and I'm going to right
click and zoom to fit just to show you what it looks like here on my screen and then I'm going
to just use the control key and mouse wheel just to kind of zoom in a little bit. A couple things
I want to show you here. First it'll show you where the table is related to another. So I have
employee related to employee pay history and the other thing is to pay attention to the little
symbols on the relationship. So here you can see a key. The key means that this is pointing to the
primary key. So chances are you know it's going to be the business entity id and it means there's
just one match on this side. When you see the little infinite sign here it looks like a little
chain link. That means there could be zero or many on this side and if you kind of look at the
columns here you're going to see that business entity id is common in both right. So these
tables related through business entity id. One business entity id an employee will match to one
or more in employee pay history. As a person gets raises more records are going to be found in this
table. So to confirm this we can also right click on the relationship and look at the properties
And here you'll see table and column specifications and I can find out what the primary unique base
table is. So here it's employee it's using business entity id as the column to match to the foreign key
which is business entity id and here's the foreign key base. This is a one-to-many match
because the business entity id is a partial primary key on that pay history table. So there
may be some tables in here where we have a one-to-one match. Let's see if I can find one
Right here's one with employee and person. See how there's a key on each side. You know I say
one-to-one but you got to be careful because really could be one employee and zero people
It all depends on how the data rules are set up. I would think just by looking at this in case
unless there's something wrong with their application that each time you add an employee
to be a person but that might not be the case. Regardless if you do find one employee you'll find
at most one person because the business entity id is the primary key in person as well as an employee
So this is a really cool trick to use to find related tables. You can then extend it. I could
say add related tables to person and you can see now it's even pulling in more tables to person
and then right click and say arrange. I'm still pulling them in
I'm still pulling them in. Getting ahead of myself here. Now I can say arrange tables and you can see now that person's
here and it's started bringing in other tables. So to do a zoom to fit