A proper relationship between two people, places or things improves the communication between them. In every real-world based application, this logic holds and a database is no different. This article discusses the different relationship types possible between database objects. The designing of these relationships between them is called modeling, and the three types of relationships are:

• one to one
• one to many
• many to many

Let's discuss each of these in detail to understand these relationships with an example.

One to One

A relationship is called a one-to-one relationship if a row of one table has exactly one related row in another table. To establish a one-to-one relationship between two objects, a primary key (PK) of one table refers to a column in another table as a unique foreign key (FK) constraint.

Some of the related real-world examples are shown here:

• Person to PAN Card - One PAN Card is always related to one and only one person. A PAN Card is the document used for filing income tax.
• Person to Passport - One passport is associated with one person and one person has one passport
• Person to driving license - One driving license uniquely belongs to one person only, who should only have one driving license.

Let's create two tables for Person and PANCard, respectively, in the SQL Server database to demonstrate this relationship. We will then insert some records into the tables. The code for this is shown below.

-- Create table name Person
CREATE TABLE Person
(
    Id INT IDENTITY PRIMARY KEY, 
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Age INT
)
--Create a table named PANCard
CREATE TABLE PANCard
(
    Id INT IDENTITY PRIMARY KEY, 
    Number VARCHAR(255),
    PersonId INT UNIQUE FOREIGN KEY REFERENCES Person(Id)
)
--Insert data to the person table
INSERT INTO Person VALUES ('Adam', 'M', 25)
INSERT INTO Person VALUES ('Bob', 'C', 25)
--Insert data to PANCard table
INSERT INTO PANCard VALUES ('P001', 1)
INSERT INTO PANCard VALUES ('P002', 2)

Next, the user can easily join both the tables to retrieve data in a consolidated way.

-- Join Person table over PANCard to get all the data together
SELECT pr.FirstName, pr.LastName, pc.Number as 'Passport#' 
FROM Person as pr
JOIN PANCard as pc ON (pr.Id = pc.PersonId)

The outcome of the above query is shown in the screenshot below.

Next, retrieve the PAN card details of the person with id equal to 1. You will notice a single record will appear as an output.

--Retrieve PAN Card details of a person with Person Id = 1
SELECT * FROM PANCard
WHERE PersonId = 1

The query outcome is shown below.

One to Many

If a row in one table has multiple related rows in another table then the relationship between the two tables is called one to many. It is easy to form this relationship simply by using the primary key of one table as a foreign key constraint inside another table.

There are many real-world one-to-many relationship-type examples which we see in our day to day life. For example:

• Person and Car - one person can have multiple cars but one car will always registered under the name of a single person (assuming car do not have shared owners)
• Customer to Account - One customer can have multiple accounts, but each account is associated with a single customer only.

Let us create the tables for the first example i.e., Person to Car. We already created the Person table above. Let's create a Car table and put some records in it.

-- ONE TO MANY RELATIONSHIP EXAMPLE
CREATE TABLE Car 
(
Id INT IDENTITY PRIMARY KEY, 
ChasisNo VARCHAR(50), 
RegistrationNo VARCHAR(50), 
RegistrationArea VARCHAR(50), 
PersonId INT UNIQUE FOREIGN KEY REFERENCES Person(Id)
);
-- Person with PersonId = 1, have two cars with him. Insert details to the table Car
INSERT INTO Car VALUES ('CHS001', 'REG001', 'Area001', 1)
INSERT INTO Car VALUES ('CHS002', 'REG002', 'Area001', 1)

The above query creates the Car table. Next, the first INSERT query gets executed and the second one fails. We get the error in the screenshot here.

Any idea What went wrong?

The uniqueness (UNIQUE) of the foreign key constraint in PersonId is the reason for the error. This makes it impossible for the database to enter the data in Cars table with the same foreign key due to which it throws an error.

To solve this problem simply remove the UNIQUE keyword from the Car table creation query and rerun all the commands. Here is the new CREATE TABLE statement.

-- ONE TO MANY RELATIONSHIP EXAMPLE
CREATE TABLE Car 
(
Id INT IDENTITY PRIMARY KEY, 
ChasisNo VARCHAR(50), 
RegistrationNo VARCHAR(50), 
RegistrationArea VARCHAR(50), 
PersonId INT FOREIGN KEY REFERENCES Person(Id)
);

Finally, get the result by selecting a specific person id from the Car table. This query is to find all the cars registered under the person with id 1.

-- Display Person 1 car's detail 
SELECT pr.FirstName, pr.LastName, Car.ChasisNo, Car.RegistrationArea, Car.RegistrationArea 
FROM Car 
JOIN Person as pr 
ON (Pr.Id = Car.PersonId)
WHERE pr.PersonId = 1

The output of the above query display multiple rows that ensure the one-to-many relationship works well.

Let's dive into the third and last section of this article which belongs to the many-to-many relationship between database objects.

Many to Many

Two tables are said to be in a many-to-many relationship if a row in one table has multiple related rows in another table and a vice-versa. It is possible to model this by introducing a new type of table inside a database called a bridge table. This bridges the two tables by setting the primary key as the combination of both the tables' primary keys. This table also hold the foreign keys within it.

Refer below the few real-world examples for a many to many relationship:

• Person and Address - one person can have multiple addresses and vice versa.
• Book and Author - one book can have multiple authors and one author can write multiple books.
• Student and subject - one student studies multiple subjects and a single subject can be studied by multiple students.
• Product and order - One product can have multiple orders and in the same way, one order can contain multiple products.

To demonstrate a many to many relationship by example, let us take two tables, Person and Address. The Person table was already created above. The Address table needs to be created. After this, create a bridge table, named PersonAddressTbl. The code is shown here.

-- MANY TO MANY RELATIONSHIP EXAMPLE
CREATE TABLE AddressTbl
(
Id INT IDENTITY PRIMARY KEY, 
Street VARCHAR(24),
Area VARCHAR(30),
City VARCHAR(40),
Country VARCHAR(30)
);
-- Create Bridge table between Address and Person
CREATE TABLE PersonAddressTbl
(
PersonId INT,
AddressId INT,
CONSTRAINT pk_personAdd PRIMARY KEY (PersonId, AddressId),
CONSTRAINT fk_person FOREIGN KEY (PersonID) References Person(id),
CONSTRAINT fk_address FOREIGN KEY (AddressID) References AddressTbl(id)
);
-- Insert records to the AddressTbl Table
INSERT INTO AddressTbl VALUES ('Strt001', 'Area001', 'City001', 'USA')
INSERT INTO AddressTbl VALUES ('Strt002', 'Area002', 'City001', 'USA')
INSERT INTO AddressTbl VALUES ('Strt003', 'Area003', 'City001', 'USA')
INSERT INTO AddressTbl VALUES ('Strt004', 'Area004', 'City002', 'USA')
-- Insert data to bridge table
INSERT INTO PersonAddressTbl VALUES (1, 1)
INSERT INTO PersonAddressTbl VALUES (1, 2)
INSERT INTO PersonAddressTbl VALUES (1, 3)
INSERT INTO PersonAddressTbl VALUES (2, 1)
INSERT INTO PersonAddressTbl VALUES (2, 2)

Now, solve the two business questions as specified here.

1. What are the different addresses of the house for Person with PersonId equal to 1?
2. Who are the people having an address with AddressId equal to 1?

To answer the first business question write and execute a query shown here.

-- QUERY 1: Fetch all the addresses which belong to Person with PersonId = 1
SELECT * FROM AddressTbl as addrs
JOIN PersonAddressTbl as brdg ON addrs.Id = brdg.AddressId
WHERE brdg.PersonId = 1

On executing the above query gives a below outcome.

Now to answer the second business question find the query here.

-- QUERY 2: Fetch all the Persons that stay in the address with AddressId = 1 
SELECT * FROM Person as pr
JOIN PersonAddressTbl as brdg ON pr.Id = brdg.PersonId
WHERE brdg.AddressId = 1

That gives the below output.

Finally, it is important to provide the layout of the complete database diagram. This is shown below. The many relationships are indicated with an infinity sign at the end of the line that connects tables.

In this article, we examined the different types of relationships between entities in a database. When you design a table, you should consider the type of relationship between it and other tables.