SQL database relationships

The relational model is based on the existing relationships between the different database tables (DB). In this section we will focus on explaining and exemplifying the different types of relationships that are essential in the administration of relational databases.

The examples in this section are based on screenshots of diagrams defined from the SQL Server Management Studio (SSMS) editor. This section is only intended to conceptually explain the foundations of the different types of relationships. Later we will explain in detail how to generate these diagrams along with their tables and relations.

1:1 relationship

In a one-to-one relationship, one record in a table is associated with one and only one record in another table.This type of relationship is mainly used for exclusive relationships when we have a large number of fields. This relationship allows us to divide the information into smaller tables with fewer fields, and facilitate the management of our databases.

This relationship is established through a Foreign Key directly linked to the Primary Key of the main table. To establish the Foreign Key in this type of relationship, it will be indistinct which table to consider main and which dependent, this being a decision of the administrator according to the data to be stored in the tables,

In this example we can see how a 1 to 1 relationship is established between the countries table and flags table, since a country can only have one flag associated with it and a flag can only belong to one country. It constitutes a widely used type of relationship that allows us to organize data efficiently and easily.

The countries table has been considered as the main table. This way, we make sure that the flags cannot be saved in the system of countries if they have not yet been registered in the database.

1:N relationship

In this type of relationship between two tables, also called a 1-to-many relationship, a record from one table can be associated with several records from another table, but not the other way around.

In this case the Foreign Key will always be defined in the secondary table wich could be repeated N times according to the relationship established with the Primary Key.

In this example we can see how a 1 to n relationship is established between the customers table and the orders table, one customer may make several purchases but each purchase is made by a single customer.

The Primary Key of a customer could be repeated several times in the Foreign Key of the orders table (customerId), in order to save the information of all the orders made by a specific client.

N:N relationship

In the n-to-n relationship type, also known as many-to-many, multiple records from one table are associated with multiple records from the another table. In this case, it is necessary to define a third intermediate table to first establish two relationships 1 to n and be able to correctly link the two main tables.

Usually the Primary Key of the third intermediate table will be a Composite key formed from the Primary Keys of the two main tables and through these keys it will form two Foreign Keys to each table with which it establishes a relationship. In addition, this third table, apart from establishing the intermediate relationships, can also store additional information such as audit information or other important data from the establishment of the relationship.

In this example we can see how an N:N relationship is established between the students and subjects table, since the students can have several subjects and the subjects can have several students.

We see that in order to establish this relationship, a third intermediate table , called StudentsSubjects is needed, which also stores additional information such as the date on which a given student enrolls in a specific subject.

We can also see that the Primary Key of the studentCourses table is the concatenation of studentId and courseId, giving rise to a Composite Key. At the same time studentId forms the Foreign Key that is related to the students table and courseId the Foreign Key linked to the subjects table.