Relationships between Tables in SQL

SQL Tutorials Tutorials 6 min read

Relationships between Tables in SQL

6 min read

We’re now entering the concept of relationships between tables in SQL. You will feel more comfortable reading this tutorial if you are familiar with and the primary, foreign and unique keys. This is a tutorial in which we will illustrate that these relationships can be categorized. We will not explore all types in detail given that this topic is rather theoretical and time-consuming in its entirety. Instead, we will study the main types of relationships between the tables you will likely need in your workplace.

Close up of someone using a laptop

 

Types of Relationships

 

Relationships between tables tell you how much of the data from a foreign key field can be seen in the related primary key column and vice versa.

Customers table

As you can see in the picture above, the “customer_id” column is a primary key of the “Customers” table. This means it contains only unique values – 1, 2, 3, and 4. The same information about “customer_id” can be found in a  table called “Sales” as a foreign key, but you will likely have a lot more than 4 rows there.

Sales and customers tables

Hence, the values from 1 to 4 can be repeated many times because the same customer can execute more than one purchase.

 

One-to-Many Relationship

 

This is an example of a ‘one-to-many’ type of relationship: one value from the “customer_id” column in the “Customers” table can be found many times in the “customer_id” column in the “Sales” table. As a relational schema, this is shown by assigning the correct symbols at the end of the arrow.

relation schema of sales and customers tables with line connecting but what symbols do we add

 

How to Display the Relationship

 

You should always read the symbols according to the direction of the relationship you are exploring.

 

The First Direction

For instance, think of it this way: a single customer could have made one purchase, but he could have also made multiple! Therefore, the second symbol, which is next to the rectangle, shows the minimum number of instances of the “Customers” entity that can be associated with the “Sales” entity.

minimum number of instances of the customers table that can be associated with the sales entity

When this symbol is a tiny line, it means “one”.

The symbol located next to the rectangle indicates the maximum number of instances that can be associated with the “Sales” entity. The angle-like symbol stands for “many”.

maximum number of instances of the customer table that can be associated with the sales entity

 

The Opposite Direction

Let’s check the relationship in the opposite direction. For a single purchase registered in the “Sales” table a single customer can be indicated as the buyer. So, we must have the name, email and number of complaints for at least one customer in the “Customers” table that corresponds to a single purchase in the “Sales” table. Therefore, the minimum number is 1.

Tables are connected by >I symbol

At the same time, we just mentioned that for a given purchase we cannot have more than one buyer, meaning that the maximum number of instances from “Sales” associated with “Customers” is also one. Hence, for every purchase, we can obtain the name, email, and number of complaints data for one customer, and we represent this logic by drawing а line for the minimum, and а line for the maximum.

and connected by II symbol

 

Cardinality Constraints

The two symbols in closer proximity to the rectangles form the relationship between the “Customers” and the “Sales” tables. In our case, it is correct to say that the “Customers” to “Sales” relationship is one-to-many, while “Sales” to “Customers” is many-to-one.

The symbols showing us relationship limitations are called cardinality constraints. There are other symbols that can be used too.

‘M’ or ‘N’ for infinite associations, or a circle for optional instances. The latter would have been the case if it weren’t necessary for a registered person to have purchased an item.

cardinality constraints

 

More Types of Relationships

 

There are some other types of relationships between tables as well – one-to-one, many-to-many, etc. This is information that we share with you as general knowledge. This is a specialized topic which is of interest mainly to advanced users.

 

Why We Use Relational Schemas

 

In summary, relational (or database) schemas do not just represent the concept database administrators must implement. They depict how a database is organized. They can be thought of as blueprints, or a plan for a database, because they are usually prepared at the stage of a database’s design.

relational schemas represent the concept database administrators must implement. depict how a database is organized. Equal blueprints for a database

Drawing a relational schema isn’t an easy job, but relational schemas will help you immensely while writing your queries. A neat and complete visualization of the structure of the entire database will always be useful for retrieving information.

 

Presenting Relationships between Tables in SQL

 

To conclude, we display relationships between tables in SQL with cardinality constraints because it makes it easier to understand. Now, that you know how they are used; you can figure out the category of a relationship by simply looking at the database schema.

The next step will be to learn how to install and use the MySQL Workbench.

Next Tutorial: How to install MySQL

Leave a Reply

Your email address will not be published.

A Free Data Science Career Guide?

Check your email shortly!