SQLTeam.com | Weblogs | Forums

Denomalization of Parent and Child the hierarchy


#1

Hello.

I need your help with problem below:

Definitions:
Parent & child relationship currently captured in the tblCGs table. (Linked accounts – these are hierarchical)
• Currently a tblCGs table allows the linking of Parent(GroupMasterCustKey) to child (CustomerKey) which may or may not exist
• This allows access between a parent and children relationship and access to shared agreements between them.
• Clients that part of the same group, the child clients don’t have their own agreements.

AssociatedClient Field: These are related clients without a hierarchical relationship.
• Currently a field on the tblCs table is too limiting and
• Clients that are related for non-SLA reasons, each client has their own agreement
• Can have implications with service retention or other issues.

Solution:

  1. To Implement a new structure where individual rows such as MIND227 - MIND227 are retrieved from the db rather than IND227 - IND229
    2.Change the structure of the tbcg
    3.Move the capture of Associatedclients Field into a separate table [ID, ClientID1 , ClientID2..... , ClientRelationshipReasonID, CreatedDate]

What I should have should like:
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3

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
INNER JOIN #tblCs AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey

This is just an example desired result. Demoralization of parts of the hierarch - Like Adjacency list ornested sets I think

Thanks

Ziggy


#2

could it be that you mean denormalization, not demoralization? (I sure hope its not the later) Example of the later: "You're worthless! Why can't you be normal!?!?!?"