The first step to write SQL is to understand the database tables meanings and relationships. This is important, as complex queries often span more than one table. Knowing the purpose of the table and how they relate to one another is key. The series starts with this introductory article.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the
AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Understand your Database Table’s Meanings and Their Relationships to Write SQL
Understanding your database is more than just knowing it is built with tables, views, and relationships. In order to write meaningful queries, you need to understand how real world data was decoded and stored in the database.
These major components provide clues we can use to translate real-world requests into actionable database queries. From understanding what content is contained within a table to understanding how to relate one table to another, knowing the database’s basic structure is key to creating queries.
When constructing queries, it is important to understand a table’s purpose or subject. Is the table used to organize employee data or a list of classes taken? In general, you can think of most tables as covering a subject, such as employees or classes.
Before writing a query, look over your database’s table names. In many cases, the names reveal the main topic or subjects of the tables. If you are looking for employees, then chances are the table will be named something akin to “
What are some of the main topics captured in this excerpt of tables from the
Object Explorer Displaying Tables
Just by reading the names, you can see this database contains information about people and their jobs.
When I confronted with creating a query, I first rely on the table names to get my bearings. Usually, I’ll notice tables that have promising names. To confirm if I’m on the right track, I’ll inspect their definition and look at their columns.
You’ll also find related tables in the same manner. A record of Employee Salaries may be in a table called
EmpSal. Keep in mind that some programmers really like to abbreviate.
Developers may follow a naming convention. Here are some naming conventions. Be on the lookout for them, as they can hint towards a table’s purpose:
- Tables containing information relating to the same topic usually start with that subject’s name.
EmployeePayHistory are excellent examples.
- Table Prefixes, such as “
Log” in the table name
ErrorLog provide clues to a table purpose. Some common prefixes are:
- Tables containing two subjects, such as
EmployeeDepartmentHistory, indicate a table could be a bridge table (See Keys and Relationships).
A table’s columns give you a lot of information. Hopefully, the designer gave the column a readable name. If so, then it is pretty easy to understand each column’s purpose. Listed below is the
Employee table. Can you tell which column is used to store the date a person was born?
Object Explorer Displaying Columns
Of course! It is the column entitled
The column’s data type also gives you hints. In this table, you can see
BirthDate is a date column and can’t be
null. That means, the column won’t hold numbers or text, just dates, so we’ll be able to use more advanced logic like finding birthdays 30 days from now and that the column will always have a value.
Here are some great tips you can use to learn more about columns:
Download your free Five Minute Guide to Database Normalization
Keys and Relationships
In our example, we talked about finding all the departments that an employee has worked in. By inspecting the table names, it seems logical that we would want to look at the
Department tables, but how are they related?
What is the Database Relationship?
This is where it makes sense to review the relevant table’s primary keys to understand what values are used to identify the tables and to see if you can use foreign keys from other tables to make a relation.
Typically column names in tables are named the same. If the primary key is
HardwareID in one table, and you know it is related, then a good start is to look for
HardwareID in another table.
Check out the
Department Tables. How are they related?
First look at the
Department table and identify the primary key. You’ll quickly see that it is named
DepartmentID. Now go to the
Employee table and look to see if that column or one similarly named is listed? Do you see a
DeptID column listed? Nope!
To me, that is a huge hint. We have two islands of information and need a bridge to get between them. This is a common problem with writing SQL. Often, we find the tables that contain the end results, but fail to initially find those that bridge the gap. Further digging is needed.
In our case, we are lucky that there are other tables that have employee in their name. If you look at the above database list, you’ll see there is one named
EmployeeDepartmentHistory. Here is a picture of all three tables:
Employee Table Relationships
EmployeeDepartmentHistory is commonly called a junction or bridge table as it contains information from an intersection of both
Departments. These types of tables used to model many to many relationships (e.g. Many
Employees work in One
Department, and One
Employee could have work in Many
Departments over his career).
By reading this, I now understand the
EmployeeDepartmentHistory table is going to contain
employees and the
departments they worked in.
Sometimes, a database designer will define foreign key relationships. Foreign key relationships are used to ensure that a foreign key value exists as a primary key in another table. For instance, in our database, there is a foreign key relationship on
DepartmentID is only assigned values found in the
Foreign key relationships are mainly put in place to ensure data integrity, but we can also use them to confirm the database designer intentions. What tables did they mean to relate to one another?
Object Explorer – Database Relationships
Views are a fancy way of saying shortcut! When I‘m writing queries, I always look for views to see whether I can use them in my queries. If a view doesn’t give you all the columns you need, you have two choices:
- Use the view in a query and join to other table to get the column you want.
- Look at the view’s definition and then copy that code into your query.
Unless the view covers all the columns I need, I typically don’t use option one. Going that route can cause your code to become inefficient and hard to read. This is especially so when you have views that refer to views. The SQL quickly becomes a tangled skein.
I typically look at the view’s definition, which is easy to do and then use that code as a starting point for my own queries.
Below you can see how I opened up the view definition for
Definition for View
Here is the
select statement from that view. As you can see, it provides great hints on how
department are related.
s.[Name] AS [Shift],
d.[Name] AS [Department],
FROM [HumanResources].[Employee] AS e
INNER JOIN [Person].[Person] AS p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] AS edh
ON e.[BusinessEntityID] = edh.[BusinessEntityID]
INNER JOIN [HumanResources].[Department] AS d
ON edh.[DepartmentID] = d.[DepartmentID]
INNER JOIN [HumanResources].[Shift] AS s
ON s.[ShiftID] = edh.[ShiftID]
From this view, you can see
Employee is related to
Department via the
EmployeeDepartmentHistory table. When I see relations I can use, I’ll just copy those portions of the
join statement into my own SQL.
Download your free Five Minute Guide to Database Normalization
This is one article in a series of four that explain how to organize and write SQL queries. All four articles, including this one, are listed below:
The post How to Write SQL – Understand Database Table Meanings and Relationships – Step 1 appeared first on Essential SQL.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/