Designing a table to show the Hierarchy level.

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  January 22, 2008 at 4:53 am

  #70703

  Hi, I have to create a table using three level of Hierarchy.

  The table has to be designed from an application drop down list.

  The sample is like this.

  Masters Admission

  ----company ----Hospital

  Branches Refferal Orders

  Patients--Patients Name Admitorders

  Patientcontact Therapy --Physicial Therapy

  Routes --Speech Therapy

  Users

  Physician--Physicians

  Physician types

  Region

  county

  I have to store the names of the above mentioned starting from

  Masters to Speech Therapy in a table.

  Create table test (Id int identity(1,1),Name varchar(50),Order_level int)

  insert into test (Name,Order_Level) select 'Masters',1

  insert into test (Name,Order_Level) select 'company',2

  insert into test (Name,Order_Level) select 'Branches',3

  insert into test (Name,Order_Level) select 'Patients Name',4

  insert into test (Name,Order_Level) select 'Patientcontact',5

  insert into test (Name,Order_Level) select 'Routes',6

  insert into test (Name,Order_Level) select 'Users',7

  insert into test (Name,Order_Level) select 'Physicians',8

  insert into test (Name,Order_Level) select 'Physician types',9

  insert into test (Name,Order_Level) select 'Region',10

  insert into test (Name,Order_Level) select 'county',11

  insert into test (Name,Order_Level) select 'Admission',12

  insert into test (Name,Order_Level) select 'Hospital',13

  insert into test (Name,Order_Level) select 'Refferal Orders',14

  insert into test (Name,Order_Level) select 'Admitorders',15

  insert into test (Name,Order_Level) select 'Physicial Therapy',16

  insert into test (Name,Order_Level) select 'Speech Therapy',17

  select Name from test order by order_level

  This works fine if the drop down does not change forever.But in ever changing environment like our application its difficult to hard code the order_level. How can I design the table or dynamicaly add order_level so that it shows me the same order even if the some of the names are added or removed.

  Note:Masters and Admission never changes,The second and third level Hierarchy will be changing. This is just the sample the drop down and first leverl Hierarchy list is little lengthier and in both the direction.

• Loner

  SSC-Insane

  Points: 21279

  More actions

  January 22, 2008 at 10:15 am

  #770546

  My old experience tells me not to use 1, 2, 3 ... in the order_status, put in 100, 200,..because in case you need to put something in between 2 and 3, then you are stuck.