Data Integrity, Implementation of Relations in Microsoft SQL Server

In the database domain, relationships - often called dependencies - essentially contribute to data integrity. In mathematics, relations are subsets that are not empty and are part of the Cartesian product of sets. However, when talking about databases, the term "relationships" is less often used unless you are talking to fellow experts. Despite the similarities, the mathematical relationships and those in MSSQL - Microsoft SQL Server differ significantly. Instead of storing all the data in one table, consider creating multiple tables and linking them using relationships. This approach offers significant advantages, especially in terms of data integrity. In this way, you minimize empty fields in tables and data redundancy.

The best database administrators and developers follow the golden rule: "Enter data once, but use it as many times as necessary." This principle applies to database records. For example, imagine that you have to enter customer first and last names into multiple tables - this would take a lot of time and your tables could become unwieldy. Thanks to the relationships between the tables, you can enter the customer's first and last name only once, and then use that data in other tables where necessary. This simplifies data entry, speeds up tasks and ensures a more professional workflow. Your tables become more concise, with fewer empty fields and less duplicate data. Even if you create more tables, maintaining and hosting your database becomes easier.

Moreover, relationships make life easier for developers and all users of your database. Therefore, it is crucial to have the experience and talent to accurately translate business logic into tables and their relationships. Examples such as the Northwind or AdventureWorks databases serve as excellent 
models for understanding relationships. You can download them here. If you are new to database design, I recommend installing these sample databases for learning purposes. Start by exploring the relationships between the tables within these databases. Remember that as a developer you can't always 
rely on database administrators to create your tables, you have to master this skill yourself.

Data Integrity - Relations

In database design, relationships between tables play a crucial role in maintaining data integrity. Let’s explore the common types of relationships:

One-to-One (1:1): In a one-to-one relationship, each record in one table corresponds to exactly one record in another table. This type of relationship is relatively rare but can be useful for scenarios where data needs to be split into separate tables for organizational or security reasons. For example, consider a database where each employee has a unique employee ID, and there is a separate table for storing sensitive employee information such as salary details. 

One-to-Many (1:N): In a one-to-many relationship, each record in one table can be associated with multiple records in another table. This is the most common type of relationship. For instance, in an e-commerce database, each customer can have multiple orders, but each order belongs to only one customer.

Many-to-Many (N:N): In a many-to-many relationship, multiple records in one table are related to multiple records in another table. To implement this relationship, an intermediary table, often called a junction or bridge table is used. For example, consider a music library database where songs can belong to multiple playlists, and each playlist contains multiple songs. Choosing the right type of relationship depends on the specific requirements of your application. Consider factors such as data redundancy, performance, and ease of querying when designing your database schema.

Unlike Microsoft Access, which provides a graphical interface for managing relational databases, Microsoft SQL Server relies on the Database Diagrams tool. It’s essential to create at least one diagram in your database to visualize all tables and their relationships. These diagrams help you understand the database structure and ensure data consistency. You can add tables to the diagram, define relationships, and manage them using the Database Diagrams feature. Additionally, you can create relationships using T-SQL language commands directly in your SQL scripts. Remember that well-designed relationships contribute to efficient data management, reduced redundancy, and improved query performance.

One to One Relationships: Theory and Implementation in Practice

A one-to-one relationship (1:1) in databases is a relationship in which each record in one table is related to exactly one record in another table, and vice versa. This type of relationship ensures that for every entry or instance in one table there is exactly one corresponding entry or instance in the other table. E.g., Each country has exactly one capital, but each capital is also the capital of exactly one country. Each person has a unique set of fingerprints. But each set of fingerprints also identifies exactly one person. For many websites, one email address is associated with exactly one user account. Each user account is identified by its e-mail address. This type of relationship is less common in data modeling, but is used in specific situations where it is necessary to maintain the uniqueness and precision of the data.

Start Microsoft SQL Server, then SSMS – SQL Server Management Studio and create the Corporation database. Create in the Designer the first Employees table consisting of three columns:

ID_Employee (int)

First Name (nvarchar (20))

Last Name (nvarchar (25))

Set the primary key and set the Identity Specification to Yes. Next, create another table and name it Security. The columns are:

ID_Security (int)

Password (nvarchar (50))

Emloyee (int)

Set the ID_Security column as the primary key and its Identity Specification property to No. Notice the Employee column. For your relationship to be one-to-one, this column must be Unique. Otherwise, the relationship you create with the Employee column will be a one-to-many relationship. So right-click on the Employee column. In the drop-down menu,

• click on Indexes/Kays… 

• click on the Add button

• click on the item IX_Security*

• Set the Type property to Unique and the Columns property to Employee.

Save and close the spreadsheet. Create a new diagram and name it Diagram1. Add the Employees and Security tables. Connect the ID_Employee column of the Employees table to the Employee column of the Security table. If you have completed all the above instructions, congratulations, you have created a one-to-one relationship between the Employee and Security tables. Once you have created the tables and connected them, you can enter the following data:

Employees table:

First Name: Manuel, Demi

Last Name: Radovanovic, Moore

Security table:

ID_Security: 101, 102

Password: ‘1234’, ‘4321’

Employee: 1, 2

If you try to enter the same number in the Employee column, you will get an error. This makes this relationship a one-to-one relationship. You can see what all this looks like in the following video.

Microsoft SQL Server - 7. How To Make the Relationship One-To-One using SSMS?

You can do this same example by creating the following SQL script and executing it.

-- Create Corporation database, tables and one-to-one relationship between tables

-- 2024-05-24 Manuel Radovanovic

-- Create database Corporation
USE master
CREATE DATABASE Corporation
GO

-- Create table Employees
USE Corporation 
GO
CREATE TABLE Employees
(
 ID_Employee int PRIMARY KEY IDENTITY NOT NULL,
 FirstName nvarchar(20),
 LastName nvarchar(25)
)

-- Create table Security
USE Corporation
GO
CREATE TABLE [Security]
(
 ID_Security int PRIMARY KEY,
 [Password] nvarchar(20),
 Employee int UNIQUE 
)

-- Create relationship one-to-one between tables

USE Corporation

GO

ALTER TABLE [Security]

ADD CONSTRAINT FK_Security_Employess

FOREIGN KEY (Employee) REFERENCES [Employees](ID_Employee)

GO

-- Insert some data into Employee table

USE Corporation

GO

INSERT INTO Employees

VALUES ('Manuel','Radovanovic'),

       ('Demi','Moore')

GO

-- Insert some data into Security table

USE Corporation

GO

INSERT INTO [Security]

VALUES (101,'1234',1),

       (102,'4321',2)

GO

SELECT * FROM Employees
SELECT * FROM [Security]

You can see what it all looks like in the following video.

Microsoft SQL Server - 8. How To Make the Relationship One-To-One using T-SQL?

One to Many Relationships: Examples and Best Practices

One-to-many relationship (1:N) in databases means a connection between two tables where one row in the first table has several related rows in the second table. In other words, each record in the first table can be related to multiple records in the second table, but each record in the second table is related to only one record in the first table. E.g. Each client can have multiple orders, but each order belongs to exactly one client. Or each product category can contain multiple products, but each product belongs to exactly one category. This type of relationship is very common in data modeling and enables efficient organization of information in the database.

Start Microsoft SQL Server, then SSMS – SQL Server Management Studio and create the new Corporation database. Create in the Designer the first Companies table consisting of two columns:

ID_Company (int)

Company (nvarchar (30))

Set the primary key and set the Identity Specification to Yes. Next, create another table and name it CompanyPhones. The columns are:

ID_CompanyPhone (int)

Company (int)

CompanyPhone (nvarchar (15))

Set the ID_CompanyPhone column as the primary key and its Identity Specification property to Yes.
Save and close the spreadsheet. Create a new diagram and name it Diagram1. Add the Companies and CompanyPhones tables. Connect the ID_Company column of the Companies table to the Company column of the CompanyPhones table. If you have completed all the above instructions, congratulations, you have created a one-to-many relationship between the Companies and CompanyPhones tables. Once you have created the tables and connected them, you can enter the following data:

Companies table:

Company: McDonald’s, Coca Cola

CompanyPhones table:

Company: 1, 1, 2

CompanyPhones: 0123456789, 0123456800, 9876543210

If you try to enter the same number in the Company column, you will not get an error. This makes this relationship a one-to-many relationship. You can see what all this looks like in the following video.

Microsoft SQL Server - 9. How To Make the Relationship One-To-Many using SSMS?

You can do this same example by creating the following SQL script and executing it.

-- Create Corporation database, tables and one-to-many relationship between tables
-- 2024-05-24 Manuel Radovanovic

-- Create database Corporation

USE master
CREATE DATABASE Corporation
GO

-- Create the table Companies 

USE Corporation

Go

CREATE TABLE Companies

(

             ID_Company int PRIMARY KEY IDENTITY NOT NULL,

             Company nvarchar(30)

)

-- Create the table CompanyPhones 

USE Corporation

Go

CREATE TABLE CompanyPhones

(

             ID_CompanyPhones int PRIMARY KEY IDENTITY NOT NULL,

             Company int,

             CompanyPhone varchar(15)

)

-- Create relationship one-to-many between Companies and CompanyPhone

USE Corporation

Go

ALTER TABLE CompanyPhones

ADD CONSTRAINT FK_CompanyPhones_Company

FOREIGN KEY (Company) REFERENCES Companies(ID_Company)

Go

-- Insert some data into Company table

USE Corporation

GO

INSERT INTO Companies

VALUES ('McDonald''s'),

       ('Coca Cola')

Go

-- Insert some data into Company table

USE Corporation

GO

INSERT INTO CompanyPhones

VALUES (1,'012 345 6789'),

       (1,'012 345 6800'),

       (2,'987 654 3210')

Go

SELECT * FROM Companies

SELECT * FROM CompanyPhones

You can see what it all looks like in the following video.

Microsoft SQL Server - 10. How To Make the Relationship One-To-Many using T-SQL?

Many to Many Relationships: Understanding and Practice

A many-to-many relationship (M:N) in the context of relational databases, such as those managed by SQL - Structured Query Language databases, denotes a situation in which each record in one table can be related to multiple records in another table, and vice versa. In other words, multiple records in one table are related to multiple records in another table. E.g. Each professor can teach several subjects, but each subject can be taught by several professors. One author can write several books, but each book can have several authors. Each student can attend several courses, but each course can also have several students.

How would you resolve this many-to-many relationship between these entities? Simply, you introduce a so-called junction table, also known as a "cross-reference table", which serves as an intermediary between these two tables. This table contains combinations of keys from both tables and allows efficient linking of records between them.

Start Microsoft SQL Server, then SSMS – SQL Server Management Studio and create the Bookstore database. Create in the Designer the first Authors table consisting of three columns:

ID_Author (int)

First Name (nvarchar (20))

Last Name (nvarchar (25))

Set the primary key and set the Identity Specification to Yes. Next, create another table and name it Books. The columns are:

ID_Book (int)

Title (nvarchar (50))

Set the ID_Book column as the primary key and its Identity Specification property to Yes. Also create a junction table and name it AuthorsBooks.

ID_Author (int)

ID_Book (int)

Pay attention. Select both columns: ID_Author and ID_Book and then set the primary key once for both columns. Set the Identity Specification to No. Save and close the spreadsheet. Create a new diagram and name it Diagram1. Add the Authors, Books and AuthorsBooks tables. Connect the ID_Author column of the Authors table to the ID_Author column of the AuthorsBooks table. Add connect the ID_Book column of the Books table to the ID_Book column of the AuthorsBooks table. If you have completed all the above instructions, congratulations, you have created a many-to-many relationships between the Authors, AuthorsBooks and Books tables. Once you have created the tables and connected them, you can enter the following data:

Authors table:

First Name: Manuel, Marissa

Last Name: Radovanovic, Powell

Books table:

Title: C# for Beginners, Microsoft SQL 2016 for Beginners

AuthorsBooks table:

ID_Author: 1, 1, 2

ID_Book: 1, 2, 2

If you try to enter the same number in any column, you will not get an error. This makes this relationship a many-to-many relationship. You can see what all this looks like in the following video.

Microsoft SQL Server - 11. How To Make the Relationship Many-To-Many using SSMS?

You can do this same example by creating the following SQL script and executing it.

-- Create Corporation database, tables and one-to-many relationship between tables

-- 2024-05-24 Manuel Radovanovic

-- Create database Corporation

USE master
CREATE DATABASE Bookstore
GO

-- Create the table Authors

USE Bookstore

GO

CREATE TABLE Authors

(            ID_Author int PRIMARY KEY IDENTITY NOT NULL,

             FirstName nvarchar(20),

             LastName nvarchar(25)

)

-- Create the table Books

USE Bookstore

GO

CREATE TABLE Books

(            ID_Book int PRIMARY KEY IDENTITY NOT NULL,

             Title nvarchar(50)

            

)

-- Create the table AuthorsBooks

USE Bookstore

GO

CREATE TABLE AuthorsBooks

(            ID_Author int,

             ID_Book int

             PRIMARY KEY (ID_Author, ID_Book)

              

)

-- Create relationship many-to-many between Authors, Books and AuthorsBooks tables

USE Bookstore

GO

ALTER TABLE AuthorsBooks

ADD CONSTRAINT FK_AuthorBooks_Authors

FOREIGN KEY(ID_Author) REFERENCES Authors(ID_Author)

GO

USE Bookstore

GO

ALTER TABLE AuthorsBooks

ADD CONSTRAINT FK_AuthorBooks_Books

FOREIGN KEY(ID_Book) REFERENCES Books(ID_Book)

GO

-- Insert some data into Authors table

USE Bookstore

GO

INSERT INTO Authors

VALUES ('Manuel','Radovanovic'),

      ('Marrisa',' Powell')

GO

-- Insert some data into Books table

USE Bookstore

GO

INSERT INTO Books  

VALUES ('C# for Beginners'),

      ('Microsoft SQL 2016 for Beginners')

GO

-- Insert some data into AuthorsBooks table

USE Bookstore

GO

INSERT INTO AuthorsBooks  

VALUES (1,1),

       (1,2),

       (2,2)

GO

SELECT * FROM Authors

SELECT * FROM Books

SELECT * FROM AuthorsBooks

You can see what it all looks like in the following video.

Microsoft SQL Server - 12. How To Make the Relationship Many-To-Many using TSQL?