Relationships in SQL - complete guide with examples

21st November 2024

In this article we take a look at the different types of database relation used in app development.

Understanding database relations & how & when to implement them is critical if you want to build a fast, reliable & scalable database for your app.

There are five types of relations used in database design: one-to-one, one-to-many, many-to-one, many-to-many & self referencing relationships.

In the rest of this article we will take a deep dive into how each of these relationships work. We will also take a look at how to implement these relationships for your app using Dittofi’s visual database design tools.

check out database templates

Get inspired by Dittofi’s database templates.

What is a relation in a database?
Types of relation in a database
- One-to-one
- One-to-many (many-to-one)
- Many-to-many
- Self referencing
Create relationship – use Dittofi to generate SQL
What next?

What is a relation in a database?

There are many different types of database e.g. flat file, relational, columnar etc. In this article we are looking exclusively at relational databases.

A relational database organises data into rows & columns which collectively form a table, or a set of tables that are used to store data for your app. Tables are known as entities, the rows are the records & columns are known as properties. The diagram below shows an example of this. In the example, there is an entity called users which has the properties id, email, first_name, last_name & Password.

Data for this entity is shown below in a table format.

The term relation is used to describe the relationships between different entities or tables in a relational database. For example, in the diagram below, there are two entities, Short term rentals & Users. The relation between these entities indicates that one user can have many short term rentals.

An Entity Relationship Diagram, otherwise known as an ERD, ER Diagram, or ER Model is a graphical representation of how entities relate to each other within a database.

ER models are regularly used in database design. This is because ER Models make it relatively easy to abstract out the data structure for your app & to view all of the relations between your data tables. Below is an example of a simple ER model for a learning management system displayed within the Dittofi Design Studio.

Note, each of the lines on the graphic represents a relationship between different entities in the database. In Dittofi you can view your database in either a spreadsheet / table style view or as entities, depending on your preference & what it is that you’re looking to do.

You can implement the design for your apps relational database using Dittofi’s visual app build OR using SQL which stands for Structured Query Language. SQL is a standard language for database creation & manipulation.

Type of database relationship

There are three main types of database relationship:

One-to-one
One-to-many
Many-to-many

One-to-one relationships

One-to-one relationships are those where one row of data in one table is related only to one row of data in another table. A practical example of a one-to-one relationship is the person to passport relationship.

One person will have one passport – so one table storing a list of people & one storing a list of their passports.

One-to-many relationships

One-to-many relationships are those where one row of data in one table is related to many (one or more) rows of data in another table. A practical example of a one-to-many relationship is the class to student relationship.

At college, one student will take many classes. So one table that contains a list of students, will be related to a table listing out a set of classes.

Notice that many to one relationships are a special case of one to many relationships that go in the opposite direction.

Many-to-many relationships

Many-to-many relationships are those where many (one or more) rows of data in one table are related to many (one or more) rows of data in another table.

A practical example of a many-to-many relationship is a table that stores a list of many university courses & a table that stores a list of many student information. A Student can take multiple (many) Courses and a Course can have many attendant Students.

You may also encounter two other kinds of relations:

A many-to-one relationship which, in fact, is a special case of a one-to-many relationship.
A self-referencing relationship which occurs when only one table is involved.

Let’s look closer at the different types of relationships & learn how to implement these for our app. One-to-many relationships are the most common type of relationship that comes up in app development so let’s start with these.

check out database templates

Use Dittofi’s database design templates to build your app.

Create relationships - use Dittofi to generate SQL

In this section, we are going to use Dittofi to create each of the different types of relationships. In each case, this can be done by using Dittofi’s visual database design tools or by writing custom SQL. We will show you how to do both.

Implementing one-to-many relationships

In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders.

In an ER model the one-to-many relationship looks like the diagram below.

In this example, the CustomerID’s field in the Customers table contains a set of unique values. The uniqueness is ensured by the field type being set to an auto incrementing number. So each new record in the Customers table will be automatically assigned a unique auto incrementing number.

Since the CustomerID is 100% unique, & can be used to lookup any individual customer in the table, we call this the primary key. The primary key connects to what is known as the foreign key in the Orders table. The foreign key is called “foreign”, because the key does not originate in the Orders table. The foreign key is in fact just a reference to the primary key in the Customers table i.e. no Customers table, no need for a foreign key in the Orders table.

The one-to-many relationship looks like the following. One record connects to one or many orders.

Note that because relationships go both ways, a one-to-many relationship is the same as a many-to-one relationship.

Implementing one-to-many relationships in Dittofi & SQL

To implement a one to many relationship in SQL, the easiest way to do this is with Dittofi’s visual database builder where you can create an ER model. To do build a one-to-many relationship, create three tables & then use drag & drop to create the one-to-many relationship in your app. The video below shows how to do this.

Alternatively, if you’re an engineer, you can write SQL code to do the following:

Create two tables (Customers table and Orders table) with their own primary keys.
Add a foreign key on a column in the Customers table based on the primary key of Orders table. This will mean that the Customers table can have one or more records related to a single record in the Orders table.

				
					CREATE TABLE customers (
  id   serial,
  name varchar NOT NULL
);

CREATE TABLE orders (
  id   serial,
  customer_id INT NOT NULL,
  name varchar NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

You can then take this SQL code & copy & paste it into Dittofi to create your tables. The video below shows how to do this.

Implementing one-to-one relationships

In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a government database, only one Person will have one Passport, and each Passport is assigned to only one person.
A one-to-one relationship looks like this in the relationships graph:

In this example, the primary key in the Persons table is the Person ID. This table has been designed so that the Person ID is of type auto increment. This means that each new Person will be assigned a unique, automatically incrementing number. In the Passports table, the Person ID field is a foreign key.

This relationship returns related records when the value in the Person ID field in the Passports table is the same as the Person ID field in the Person’s table. The relationship looks like the following image:

Implementing one-to-one relationships in Dittofi & SQL

In this example, we are going to implement a one-to-one relationship between a Person & their Passport. One person can have one passport.

To implement this one to one relationship in SQL, we can use Dittofi’s visual database builder. To build this relationship, first create two tables: Persons table & Passports table. Make sure the tables have all of the relevant fields & then use Dittofi’s visual app builder to drag & drop builder to create the relationship between the two fields.

Alternatively, if you are a developer, you can create two tables using SQL code, & then define a simple primary foreign key relationship between them, and set the foreign key column to be unique. A sample of this SQL is shown below.

				
					CREATE TABLE person (
  id   serial,
  first_name varchar NOT NULL,
  last_name varchar NOT NULL
);

CREATE TABLE passport (
  id   serial,
  country varchar NOT NULL,
  date_of_issue TIMESTAMPTZ NOT NULL,
  person_id INT NOT NULL,
  FOREIGN KEY (person_id) REFERENCES person (id),
  UNIQUE (person_id)
);

The video below shows how you can use this SQL code directly inside Dittofi to create two tables & their joins.

Implementing many-to-many relationships

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between students and classes: students can register for many classes, and a class can include many students.

Relational databases normally don’t allow you to implement a many to many relationship directly between two tables.

To get around this problem, many to many relationships require that you use a third table called a join table. A join table includes a match field that contains the values of the primary keys of the two tables that it joins. In the joins table these values are called foreign keys. These foreign key fields are populated with data as records in the join table are created from either table it joins.

For example, in our example of students & classes. We will have a Students table that contains records for each student & a Classes table that contains a record for each class. Then in the middle we will have a join table called Enrollments. The Enrollments table will contain the primary keys for the student_id & the classes_id. This will allow it to keep track of which student enrolls in each class & which class contains each student.

Notice how the student_id in the Enrollment table can be used to uniquely identify the student in the Students table & the classes_id in the Enrollment table can be used to uniquely identify the class in the Classes table.

The ER model for this will look like the following. Notice further how the student_id field is the primary key when it is inside of the Students table & the classes_id field is the primary key when inside the Classes table. However, both the student_id & the classes_id are foreign keys inside of the Enrollments table.

Implementing many-to-many relationships in Dittofi & SQL

In this example, we are going to implement a many-to-many relationship between Students & Courses. Students can register for many courses & Courses can contain many students.

To implement this many to many relationship in SQL, you can do this using Dittofi’s visual database builder. Here you can use the visual builder to create three tables such as a Students table, a Classes table & an Enrollments table (the join table).

You can then use Dittofi’s visual ERD builder to draw relationships between the different tables. To do this follow the video tutorial below.

Alternatively, if you are a developer, you can first create two tables using the following SQL code.

				
					CREATE TABLE students (
	id serial,
	first_name VARCHAR NOT NULL,
	last_name VARCHAR NOT NULL
);

CREATE TABLE classes (
	id serial,
	title VARCHAR NOT NULL,
	description VARCHAR NOT NULL
);

Next, create a junction table Enrollments that will map these two tables together by referencing the primary keys of both tables.

				
					CREATE TABLE enrollments (
	id serial,
	student_id INT NOT NULL,
	class_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students (id),
	FOREIGN KEY (class_id) REFERENCES classes (id)
);

The video below shows how you can use this SQL code directly inside Dittofi to create the three tables & their joins.

What to do next?

Database design is absolutely fundamental in your app design. It is analogous to the foundations that your house is built on. If the foundations of your app are not developed properly, your app will fall over & fail when you start to onboard more & more users.

Unlike traditional no-code, Dittofi lets you build professionally designed database structures for your app. These will enable you to build an enterprise grade foundation for your app.

To help design an enterprise grade database for your app, we have a set of database templates that you can use to build all kinds of apps (two-sided marketplaces, edtech apps, social media apps etc.). You can either use these templates to form the base of your app, or you can use the templates for inspiration.

Article by

James Virgo

Co-Founder of Dittofi