Category and Sub Category Relationship Sql Server Many to Many

This post is related to maintain the important scenario of today object oriented programming. Category and Sub Category up to n times.

As I check lot of post related to maintaining the relationship. I found that most of then is using Standard Strategy for Many To Many but this not successful when  we are going to deep down up to 5 or more , Because two hold one many to many relation ship we need at least Three Tables.

Let us understand via a case first,

Table 1- Shopping Category.

 Case 1- Main Sopping Category(Electronics Good, Clothes, FMCG)

 Case  2- Now all the have Sub Categories.

 Case 3- These Sub Categories can have also Sub categories and up to n.

So maintain that kind of relationship will become complected in Many to Many relationship database design. Because every time we have to create a new table and relationship table.

So what is the solution. we used to find the solution after getting the Problem. I got the same problem in my office task. 

Task 1- Create a Store Procedure for insertion of Category and Sub Category with condition that a Parent category can not be inserted in child category at any level.

So for this task i choose two table architecture 

1- Category Details- Category Id and Category Name.

2- Relation Ship Table - Child Category and Parent Category ID.

Now to Compete this Task I developed a Store Procedure.

For Insertion -

Complete Code for SP is Given below-

USE [DIZSWEB]GO/****** Object:  StoredProcedure [dbo].

[insertIntoRelation_category]    

Script Date: 2/16/2020 1:56:49 PM ******/

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Sawan Chauhan>-- Create date:      <16, Feb, 2020>-- Description: <Category Relationship for many to Shopping websites>
-- =============================================

ALTER PROCEDURE [dbo].[Insertintorelation_category] @childId  INT, 
                                                    @parentId INT 
AS 
  BEGIN 
      DECLARE @nextparentId INT =0; 
      DECLARE @insertstatus INT =0; 
      DECLARE @duplicate INT =0; 
      DECLARE @test1 INT =0 
      DECLARE @test2 INT =0 

      SELECT @duplicate = Count(*) 
      FROM   [dbo].[relation_category] 
      WHERE  cat_id = @parentId 
             AND relation_id = @childId 

      --print @duplicate  
      SELECT @test1 = Count(cat_id) 
      FROM   category 
      WHERE  cat_id = @parentId 

      --print @test1  
      SELECT @test2 = Count(cat_id) 
      FROM   category 
      WHERE  cat_id = @childId 

      --print @test2  
      DECLARE @ccc INT=0; 

      IF @duplicate = 0 
         AND @test1 > 0 
         AND @test2 > 0 
        BEGIN 
            WHILE @insertstatus < 1 
              BEGIN 
                  SELECT @nextparentId = [relation_id] 
                  FROM   relation_category 
                  WHERE  [cat_id] = @childId 

                  SELECT @ccc = Count(*) 
                  FROM   relation_category 
                  WHERE  [cat_id] = @childId 

                  PRINT ( 'Test1' ) 

                  PRINT @nextparentId 

                  PRINT ( 'Test2' ) 

                  PRINT @ccc 

                  IF ( @nextparentId = @parentId ) 
                    BEGIN 
                        IF ( @ccc = 0 ) 
                          BEGIN 
                              SET @insertstatus=2 
                          END 
                        ELSE 
                          BEGIN 
                              SET @insertstatus=1 

                              PRINT ( 
                          'You are trying to insert the Parent into the Child' ) 
                          END 
                    END 
                  ELSE IF ( @nextparentId > 0 ) 
                    BEGIN 
                        SET @parentId=@nextparentId 
                    END 
                  ELSE 
                    BEGIN 
                        BREAK 
                    END 
              END 

            IF @insertstatus = 0 
                OR @insertstatus = 2 
              BEGIN 
                  INSERT INTO [dbo].[relation_category] 
                  VALUES     (@parentId, 
                              @childId) 
              END 
        END 
      ELSE 
        BEGIN 
            PRINT( 'Wrong Data Passed' ) 
        END 
  END 

Task 2- I have to display the data of all category Id in Parent and Child Structure.

 As we have Parent Id, 1 All its sub branches should be show in row wise and Comma separated.

To complete this Task I also design a Store Procedure Which will evaluate this process Also .

USE [dizsweb.com] 

go 

/****** Object:  StoredProcedure [dbo].[HiherchyDiagram]  
Script Date: 2/16/2020 1:58:17 PM ******/ 
SET ansi_nulls ON 

go 

SET quoted_identifier ON 

go 

-- ============================================= 
-- Author:     
-- Create date: <16, Feb 2020> 
-- Description:  Selection Of all Parent Child 

   Category according to Category Id 
--  exec [dbo].[HiherchyDiagram] '2' 
-- ============================================= 
ALTER PROCEDURE [dbo].[Hiherchydiagram] @id INT 
AS 
  BEGIN 
      DECLARE @P INT; 
      DECLARE @Parent INT=0; 
      DECLARE @child INT=0; 
      DECLARE @Mid INT; 
      DECLARE @curvar INT; 
      DECLARE @cursorrepeat INT=1; 
      DECLARE @cursorrepeat222 INT =1 
      DECLARE @parent1 NVARCHAR(100)='' 
      DECLARE @child1 NVARCHAR(100)='' 
      DECLARE @first_p INT =0; 

      CREATE TABLE #temp2 
        ( 
           parent NVARCHAR(100), 
           child  NVARCHAR(100) 
        ) 

      DECLARE cursor_id CURSOR FOR 
        SELECT DISTINCT a.relation_id 
        FROM   relation_category a 
        WHERE  a.cat_id = @id 

      OPEN cursor_id; 

      FETCH next FROM cursor_id INTO @curvar 

      WHILE @@FETCH_STATUS = 0 
             OR @cursorrepeat222 = 1 
        BEGIN 
            SET @parent1='' 
            SET @child1='' 

            DECLARE @ccc INT=1; 
            DECLARE @id1 INT 
            DECLARE @id2 INT 

            SET @id1 =@id 
            SET @id2 =@curvar 

            --end 
            WHILE( @ccc > 0 ) 
              BEGIN 
                  --  select @Parent=a.Relation_id from Relation_Category a where a.Cat_Id=@id1 
                  SELECT @Parent = a.cat_id 
                  FROM   relation_category a 
                  WHERE  a.relation_id = @id1 

                  --print(@Parent) 
                  --select @ccc=count(*) from Relation_Category a where a.Cat_Id=@id1 
                  SELECT @ccc = Count(*) 
                  FROM   relation_category a 
                  WHERE  a.relation_id = @id1 

                  IF( @ccc > 0 ) 
                    BEGIN 
                        --print('Parent 1') 
                        SET @parent1 = Concat(@parent1, Concat(@Parent, ',')) 
                        SET @id1=@Parent 

                        --set @ccc=@ccc-1 
                        PRINT( @id1 ) 

                        PRINT( @ccc ) 
                    END 
              END 

            SET @ccc =1; 

            DECLARE @INNER INT=1; 

            WHILE( @ccc > 0 ) 
              BEGIN 
                  IF( @INNER = 1 ) 
                    BEGIN 
                        SET @child1 = Concat(@child1, Concat(@id2, ',')) 
                        SET @INNER=0 
                    END 

                  SELECT @Parent = a.relation_id 
                  FROM   relation_category a 
                  WHERE  a.cat_id = @id2 

                  PRINT( 'test' ) 

                  SELECT @ccc = Count(*) 
                  FROM   relation_category a 
                  WHERE  a.cat_id = @id2 

                  IF( @ccc > 0 
                      AND @Parent <> @id2 ) 
                    BEGIN 
                        SET @child1 = Concat(@child1, Concat(@Parent, ',')) 
                        SET @id2=@Parent 

                        PRINT( 'Test1 ' ) 

                        PRINT( @id2 ) 

                        PRINT( @ccc ) 
                    END 
                  ELSE 
                    BEGIN 
                        SET @ccc=0 
                    END 
              END 

            SET @cursorrepeat = @cursorrepeat + 1 
            SET @parent1= Substring(@parent1, 0, Len(@parent1)) 
            SET @child1= Substring(@child1, 0, Len(@child1)) 

            -- insert into #temp2 values(@child1,@parent1) 
            INSERT INTO #temp2 
            VALUES     (@parent1, 
                        @child1) 

            SET @cursorrepeat222=@cursorrepeat222 - 1 

            FETCH next FROM cursor_id INTO @curvar 

            PRINT( '@curvar Bottom' ) 

            PRINT( @curvar ) 
        END 

      CLOSE cursor_id; 

      DEALLOCATE cursor_id; 

      SELECT * 
      FROM   #temp2 
  END 

This Code only applicable for 9 id no if anyone like to use this code in his project or program for more than 9 ID he need to make some changes for getting the values according to comma separation.

Category Relation Ship Script of Table Creation is - 

USE [Dizsweb.com]

GO

/****** Object:  Table [dbo].[Relation_Category]    

Script Date: 2/16/2020 2:36:20 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Relation_Category](

[Cat_Id] [int] NULL,

[Relation_id] [int] NULL

) ON [PRIMARY]

GO

Category Holding table Scipt-

USE [TESTJFT]

GO

/****** Object:  Table [dbo].[Category]    

Script Date: 2/16/2020 2:37:16 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Category](

[Cat_id] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

[Cat_Ref_Id] [int] NULL,

PRIMARY KEY CLUSTERED 

(

[Cat_id] ASC

)WITH (PAD_INDEX = OFF,

 STATISTICS_NORECOMPUTE = OFF,

 IGNORE_DUP_KEY = OFF,

 ALLOW_ROW_LOCKS = ON,

 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Other Sql Server  - Queries-SQL Queries

https://www.youtube.com/watch?v=kOFzyIppPU4

https://www.youtube.com/channel/UChOjIMujdEzVGHRI1m2yoxQ