Relationships between Tables in SQL

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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

Originally published at https://365datascience.com