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.
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.
One to Many Relationships: Examples
and Best Practices
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:
Set the ID_CompanyPhone column as the primary key and its Identity Specification property to Yes.ID_CompanyPhone (int)
Company (int)
CompanyPhone (nvarchar (15))
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.
-- 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.
Many to Many Relationships:
Understanding and Practice
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.
No comments:
Post a Comment