Hierarchy Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Hierarchy Table

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-26 : 05:08:38

Hi,

I have a Hierarchy table. It's like this

CREATE TABLE [dbo].[USHR_ProgressNotes](
[NotesId] [int] IDENTITY(1,1) NOT NULL,
[NotesName] [varchar](200) NOT NULL,
[ControlType] [varchar](100) NULL,
[ParentNotesId] [int] NULL,
[Depth] [tinyint] NULL,
[Order] [tinyint] NULL,
[Others] [varchar](8000) NULL,
[ISCOMMON] [bit] NOT NULL,
CONSTRAINT [PK__Progress__35AB5BAA1C880743] PRIMARY KEY CLUSTERED
(
[NotesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Its a table that contain questions which contains its subsetquestion id as hierarchy

NotesId NotesName ParentNotesId Order ISCOMMON
1 Q1 Null 1 1
2 Q2 Null 2 1
3 Q3 Null 3 1
4 Q4 1 1 1
5 Q5 1 2 0

So if i want all the questions of Q1. I will get Q4,Q5 based on mapping of its immediate parent in ParentNotesId.

The table is fine. The prob is we have mapping to the questions with department. Each and every dept has one question set.
A)So if i assign NotesId 1 to Dept "Dept1" then it will Q1(parent),Q4,Q5.
B)The Prob is say i assign NotesId 1 to dept "Dept2" then it will also get Q1(parent),Q4,Q5. But say the "Dept2" maps to parentId but doesn't need its one of subset question say Q5 then output should be Q1(Parent), Q4 and not Q5.

Help me. Thanks in Advance

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-26 : 05:23:25

I have set something like this by keeping secondary table that maps dept with the notes from parent table

CREATE TABLE MapDeptNotes
(
DeptNotesId INT IDENTITY(1,1) NOT NULL,
OrgId INT NOT NULL,
DeptId INT NOT NULL,
NotesId INT
)

and also there is IsCommon field set in parent table for whether this question(Notes) is mandatory to all dept. in that case we can map only non mandatory(IsCommon=0) to that secondary table "MapDeptNotes"

Is my approach correct. When iscommon is non mandatory i will check whether that question(notes) is mapped to dept in secondary table "MapDeptNotes"

any help. Please

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources