T-SQL and denomalization of Parent and Child hierarchy Tables
-
The Problem: Parent & child relationship currently captured in tblCGs and tbcs tables. The MasterClient are held in tblCGs table while Children are stored in tblCs tables using tblCGs. GroupMasterCustKey as key and CustomerKey as Child key. • Currently a tblCGs table allows the linking of Parent using (GroupMasterCustKey) to child tblcs - (CustomerKey) table which may or may not exist. AssociatedClient Field in the tblcs table: These are related clients without any hierarchical relationship. • Currently the AssociatedClient on the tblCs table is too limiting varchar(20) and can have implications or cause issues. Proposed Solution: • To Implement a new table structure where individual rows such as MIND227 - MIND227(customer ranges) are retrieved from the table rather than IND227 - IND229 • Move the Associatedclients Field in tblcs into a separate table with the following fields (ID, ClientID1, ClientID2..... , ClientRelationshipReasonID, and CreatedDate) Please see the sample code below IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs --Create ClientGroups table. CREATE TABLE #tblCGs ( [GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN] [GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '), [GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '), ) -- Populate the table with values. INSERT INTO #tblCGs VALUES (988, N'MIN036', N'MIND227', N'MIND229') , (668, N'08035635', N'31036422', N'31036422') ,(669, N'08035635', N'31035623', N'31035623') -- Create Customer table. CREATE TABLE #tblCs ---Customers ([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN] ,[CompanyName] varchar(300) NULL ,[AssociatedClient] varchar(20) ,[CreatedOn] Datetime ) --Populate the table with values. INSERT INTO #tblCs VALUES (72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000') ,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000') ,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000') ,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550') ,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837') --- Current Queries SELECT * FROM #tblCGs AS CG IN
-
The Problem: Parent & child relationship currently captured in tblCGs and tbcs tables. The MasterClient are held in tblCGs table while Children are stored in tblCs tables using tblCGs. GroupMasterCustKey as key and CustomerKey as Child key. • Currently a tblCGs table allows the linking of Parent using (GroupMasterCustKey) to child tblcs - (CustomerKey) table which may or may not exist. AssociatedClient Field in the tblcs table: These are related clients without any hierarchical relationship. • Currently the AssociatedClient on the tblCs table is too limiting varchar(20) and can have implications or cause issues. Proposed Solution: • To Implement a new table structure where individual rows such as MIND227 - MIND227(customer ranges) are retrieved from the table rather than IND227 - IND229 • Move the Associatedclients Field in tblcs into a separate table with the following fields (ID, ClientID1, ClientID2..... , ClientRelationshipReasonID, and CreatedDate) Please see the sample code below IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs --Create ClientGroups table. CREATE TABLE #tblCGs ( [GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN] [GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '), [GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '), ) -- Populate the table with values. INSERT INTO #tblCGs VALUES (988, N'MIN036', N'MIND227', N'MIND229') , (668, N'08035635', N'31036422', N'31036422') ,(669, N'08035635', N'31035623', N'31035623') -- Create Customer table. CREATE TABLE #tblCs ---Customers ([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN] ,[CompanyName] varchar(300) NULL ,[AssociatedClient] varchar(20) ,[CreatedOn] Datetime ) --Populate the table with values. INSERT INTO #tblCs VALUES (72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000') ,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000') ,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000') ,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550') ,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837') --- Current Queries SELECT * FROM #tblCGs AS CG IN
Is it your homework?