en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


Read first, before you you use the blog

Maximize
יונ10

Written by: ronen ariely
10/06/2017 04:23 RssIcon

Many-To-Many relationship

Good day readers,

This is short post I publish as an answer to a question in the MSDN forum. In this post I will go very fast on the theoretical topic and will move to a short demo.

Note! There are a lot of much better deep publication online, and if you want to understand the topic in more depth I highly recommend to read one of these. I add links to several tutorials at the end of the post.

Background: relationships

Relationships between groups of entities is the basic of relational database (like SQL Server), which based on the relational model of data. This model organizes all the entities of the same type in a table, with a unique key identifying each row in the table as a specific entity of that type.

Relationships between different entities (for example type A and type B) can be: (1) One-To-One relation, which mean that each entity type A is related to a specific entity of type B, and that specific entity type B cannot be assigned to another entity type A. (2) One-To-Many, which means that each entity type A can be related to multiple entities of type B, but each entity of type B can only be assigned to a single entity of type A. (3) Many-To-Many relation, which means that each entity type A can be related to many entities type B and vice versa.

Relational database system implements relations by using foreign key, which point to the related unique key identifying the row in the other table (named Primary Key). When multiple rows in table B have the same foreign key pointing to the same row in table A, we Implementing a One-To-Many relationship.

Since foreign key can only point to a single row in the other table, this model cannot implement a direct Many-To-Many relationship between rows in two entities tables.

Many-To-Many relasionship

In order to implement Many-To-Many relationship using this model (based on foreign key), we use a third table (called “join table”, “junction table”, or “relation table”). Instead of direct relationship between the two entity tables A and B, we implement a One-To-Many relationships between our entity tables and the junction table.

 A good example of Many-To-Many relationship is a Role-based access control (RBAC), where entities type USER granted or denied privileges/Permissions according to SET of ROLEs assigned to them.

In this case we have two entities types USER and ROLE, and the relationship between these is Many-To-Many. Each USER can have several ROLEs, and each ROLE can be assigned to several USERs.

        

Demo: implementing Many-To-Many relationship

In this short demo I will implement a basic Users-Roles relational structure.

Let’s create the entities tables (DDL):

DROP TABLE IF EXISTS Ari_Users_Tbl;
 
CREATE TABLE Ari_Users_Tbl (
 
    UserId int IDENTITY (1,1) PRIMARY KEY
 
    , UserName NVARCHAR(10)
 
)
 
GO
 
DROP TABLE IF EXISTS Ari_Roles_Tbl;
 
CREATE TABLE Ari_Roles_Tbl (
 
    RoleId int IDENTITY (1,1) PRIMARY KEY
 
    , RoleName NVARCHAR(100)
 
)
 
GO

And we need some sample data to work with (DML)

insert Ari_Users_Tbl (UserName)
 
VALUES('Ronen') ,('Ariely') ,(pituach)
 
GO
 
insert Ari_Roles_Tbl(RoleName)
 
VALUES
 
    ('MSDN Moderator'),('TechNet Wiki guru'),('MVP'), ('Wiki Author')
 
    ,('senior consultant'),('applications & data architect')
 
    ,('Microsoft TechNet WIKI communities’ leaders'), ('Blogger')
 
GO

 Now we need to create the relation between our users and their roles. For this we will create a junction table and fill it with the relations data

CREATE TABLE Ari_UsersRoles_Tbl(
 
    RelationId INT IDENTITY (1,1)
 
    , UserId INT
 
    CONSTRAINT FK_UserId FOREIGN KEY (UserId)   
 
    REFERENCES Ari_Users_Tbl (UserId)   
 
    ON DELETE CASCADE  
 
    ON UPDATE CASCADE
 
    , RoleId INT
 
    CONSTRAINT FK_RoleId FOREIGN KEY (RoleId)   
 
    REFERENCES Ari_Roles_Tbl (RoleId)   
 
    ON DELETE CASCADE  
 
    ON UPDATE CASCADE
 
)
 
GO
 
INSERT Ari_UsersRoles_Tbl (UserId, RoleId)
 
VALUES
 
    (1,1), (1,2), (1,3), (1,4)
 
    ,(2,1), (2,3), (2,5), (2,6), (2,8)
 
    ,(3,1), (3,3), (3,4), (3,7), (3,8)
 
GO

       

See also

·         Many-to-many relationships