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
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
-- 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
