?
Solved

recursive table designing

Posted on 2005-03-02
24
Medium Priority
?
347 Views
Last Modified: 2013-12-03
Hi,

We have 3 tables. Please kindly guide me how we can redesign the tables below to allow a recursive query for pulling up memebers of an organization.

To return all members that fall under a specific vice president or any other group heads.


- President (top of the food chain) - group head
   -(2) vice president - group head
             -(2) managers per vice persident, so total of (4) managers - group head
                         -(10) employees per manager, so total of (40) employees


The columns needed are:
OperatorID - numeric identifier for each employee.
Name - name of the employee.
Team- team assigned.
Designation - role of employee.
Supervisor - which supervisor is handling the team.

_______________________________Designation_Master table (DesignationID-auto number)
DesignationID DesignationName
a01                    Manager
a02                    Employee
a03                    Vice president
a04                    President

_______________________________Operator_Master table
OperatorID OperatorName TeamID       DesignationID
OP01                  mark       r01               a02                      <------employee
OP02                  john        r01               a02                      <------employee
OP03                  smith       r02               a02                      <------employee
OP04                  karen       r02               a02                      <------employee
OP05                  mike         NULL           a01                      <------manager
OP06                  blythe       NuLL            a03                      <------VP
OP07                  gerry        NuLL            a01                      <------manager

_______________________________Team_Master table (TeamID autonumber)
TeamID TeamName SupervisorID
r01          team1          w06
r02          team2          w07


Thanks.
0
Comment
Question by:lynnton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
24 Comments
 
LVL 15

Expert Comment

by:OMC2000
ID: 13439213
I would recommend leave your data structure. Unlike Oracle MS SQL has no effective tools for recursive query, so your schema is quite acceptable and effective.

You could use query like this to get all necessary informaiton:

select OperatorID, OperatorName, TeamName, Designation, s.OperatorName
from Operator_Master o inner join Designation_Master d
on o.DesignationID = d.DesignationID
left outer join Team_Master t on o.TeamId = t.TeamID
left outer join Operator_Master s on t.SupervisorID = s.OperatorID

And you could make view over it.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13439237
OMC2000,

Please kindly explain how can I create the link o nthe present table design. (some examples please)

Thanks.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13439251
OMC2000,

For example how can we link the vice president to return all the meneagers under him and the employees under those managers.. using the present table design

1 VP -2 manager - 20 employees

Thanks.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:OMC2000
ID: 13439383
I'm creating these table to check it in work...
0
 
LVL 1

Author Comment

by:lynnton
ID: 13439419
OMC2000,

Thanks so much, I'm really lost on how to design a recursive table for organization groupings and how to populate the data to achieve one.

like
query all the memebers below VicePresident-1

VP-1 has two manager and each manager has 10 employees.


query all the memebers below vicePresident-2

VP-2 has two manager and each manager has 10 employees.  (this are totally different employees from VP-1)

Thanks.
0
 
LVL 15

Accepted Solution

by:
OMC2000 earned 2000 total points
ID: 13439642
Like this:

_______________________________Designation_Master table (DesignationID-auto number)
DesignationID DesignationName
a01                    Manager
a02                    Employee
a03                    Vice president
a04                    President

_______________________________Operator_Master table
OperatorID OperatorName TeamID       DesignationID
OP01                  mark       r01               a02                      <------employee
OP02                  john        r01               a02                      <------employee
OP03                  smith       r02               a02                      <------employee
OP04                  karen       r02               a02                      <------employee
OP05                  mike         m01           a01                      <------manager
OP06                  blythe       p01            a03                      <------VP
OP07                  gerry        m01            a01                      <------manager
OP09                  Boss         NULL           a04                      <------President

_______________________________Team_Master table (TeamID autonumber)
TeamID TeamName SupervisorID (foreign key references Operator_Master(OperatorID))
r01        team1             OP05
r02        team2             OP07
m01      Managers1       OP06
p01       Vice Presidents OP09

create view staff as
select o.OperatorID, o.OperatorName, TeamName, DesignationName, s.OperatorName Manager, SupervisorID
from Operator_Master o inner join Designation_Master d
on o.DesignationID = d.DesignationID
left outer join Team_Master t on o.TeamId = t.TeamID
left outer join Operator_Master s on t.SupervisorID = s.OperatorID


select s2.OperatorName, s1.OperatorName, s2.DesignationName, s1.DesignationName
from staff s1
left outer join staff s2
on s2.SupervisorID = s1.OperatorID
where s1.SupervisorID = 'OP06'
union all
select s2.OperatorName, s1.OperatorName, s2.DesignationName, s1.DesignationName
from staff s1
left outer join staff s2
on s2.SupervisorID = s1.OperatorID
where s1.OperatorID = 'OP06'
0
 
LVL 1

Author Comment

by:lynnton
ID: 13439836
OMC2000,

Clear and consice. Thanks so much.

Please kindly guide me which column and table needs to be auto number IDENTITIY(1,1).

_______________________________Designation_Master table (DesignationID-auto number)
DesignationID DesignationName


_______________________________Operator_Master table
OperatorID OperatorName TeamID       DesignationID


_______________________________Team_Master table (TeamID autonumber)
TeamID TeamName SupervisorID (foreign key references Operator_Master(OperatorID))




Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13439982
I would make these field auto number, but it's your choice, if you use character prefix in them, then can't be autonumbers.
There is nothing wrong, if you generate these ids using current max value for some group determined by prefix.

Designation_Master.DesignationID
Operator_Master.OperatorID
Team_Master.TeamID

Also,

Operator_Master.TeamID foreign key references Team_Master(TeamID)
0
 
LVL 1

Author Comment

by:lynnton
ID: 13440054
OMC2000,

You're simply the best, I'll start populating it manually and come back if I get into trouble on creating a add/search query.

Thanks.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13448733
OMC2000,

I've finish populating it manually. I've realize we need a maintenance feture "add,edit,delete" for users to constantly update the database.

here's what I think I need to do (I have a feeling there's somthing wrong/missing)

degination maintenance
add/edit/delete record on column:
-designation Name (ID is autonumber)

team maintenance
add/edit/delete record on columns:
-team Name (ID is auto nubmer)
-Supervisor ID

I'm not sure how to approach Operator Mater.. Did I do the correct design for creating maintenance for the tables above?

Operator master is a complex table I dont think users will understand how it works? Do we need to design somthing easier for them to understand?

Thanks.


Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13449618
lynnton,
there is nothing too complex in your design, more over it's the most regular design for things like this.


team maintenance
add/edit/delete record on columns:
OperatorName (ID is auto nubmer)
TeamID
DesignationID

TeamID and DesignationID could be obtained from the lists of available Teams and Designations in User Interface. Usually they are represented as dropdowns. Where ID is a key and Name is text to display.

Users don't need to understand database schema. They should understand user interface.
If you give them set of input boxes for names input and set of dropdowns for value selection and these controls are reasonable for them (and they are!) users must be happy:)


0
 
LVL 1

Author Comment

by:lynnton
ID: 13449852
OMC2000,


would something like this for user interface inorder to add/edit a record to Operator Master:
user will input the following /select from combo box

columns:
operatorid-
operatorname-
team-
supervisor name-
designation name-

Thanks.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13450045
OMC2000,

Updated

columns for adding a record on Operator Master table:
operatorid- manual input (this is random so we need it manualy inputed)   :-)
operatorname- manual input
team name-  drop down combo (user can choose for null value)
supervisor name- drop down combo (user can choose for null value)
designation name- drop down combo (user can choose for null value)

Editing:
operatorid- can't be change **
operatorname- manual input
team name-  drop down combo (user can choose for null value)
supervisor name- drop down combo (user can choose for null value)
designation name- drop down combo (user can choose for null value)

Please kindly guide me how to create the interface so that we can generate an sql query to add/edit the recond in question properly. I'm affraid I could left something important and later we can't edit/ add because of this.

Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13450084
I thought you will have three separate forms for Designation, Team and Opetator.
First of all designation should be defined.
Then top level operator, I guess President; then team of vice presidents Supervised by President; then teams for them; then manager; then teams for managers; then employees;

OperatorID, if it's not an autonumber, then yes.

all other controls should be defined in appropriate form.

Team and Supervisor are joined into a single instance at point of employee definition.  For example, you can't select team supervised by one person and another supervisor.

For user concenience you could use dropdown where team name and supervisor name are concatenated into single string when you define team for new employee.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13450225
OMC2000,

Yes, three different forms.

-designation master form.
-operator master form.   ---this part is i'm having problems with.
-team master form.

*designation master form.
1. What do you mean by top level ? ( i can understand you want something then we need to order it by access level)

*team master form
1. I'm kinda lost on ..you can't select team supervised by one person and another supervisor. (this is related to editing??)

2. This is a good idea. (concatinate for adding new employees)

Thanks.



0
 
LVL 1

Author Comment

by:lynnton
ID: 13450690
OMC2000,

I got it now!!!  Please read below for system flow.

*designation Form
-add, user will input designation name. then click add.

user select one record to edit/delete from the datagrid.
-edit, user will type the new designation name. then click save.
-delete user will click delete. ( I have a question what will happen to those employees who has this particular degination, if the user deletes it? what do we do in this situation?)

*Team Form
-add, user will manually input team name and choose 1 supervisor name from the list. then click add.

user select one record to edit/delete from the datagrid.
-edit, user will type the new team name. then click save.
-delete user will click delete. ( I have a question what will happen to those employees who are part of the team that where deleted? if the user deletes it. what do we do in this situation?)

*Operator Master Form
User will be force to choose for standard or supervisor employee status.
*-if standard, supervisor column will not be visible (thus eliminating mismatch supervisor)
-add, user will manually input operatorID, Name, drop down team list, and drop down designation name.

user select one record to edit/delete from the datagrid.
-edit, user will manually change OperatorID, Name, drop down team list, and drop down designation name.
--delete user will click delete.

*-if supervisor, user will now be able to see supervisor name and select one from the list.
-add, user will manually input operatorID, Name, drop down team list, and drop down designation name. (user can select null values for Team and supervisor. (i.e. president account was added)

user select one record to edit/delete from the datagrid.
--edit, user will manually change OperatorID, Name, drop down team list, drop down supervisor and drop down designation name.
---delete user will click delete.

Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13450925
"( I have a question what will happen to those employees who has this particular degination, if the user deletes it? what do we do in this situation?)"


You have two options:
1. prevent user from such records deleting. If you just specify referencial integrity constraints you'll get error message about existing child record(s). You can catch it and give user frienly message: like delete users first.
2. delete all children records on delete of parent record. Then you should add option ON DELETE CASCADE to referencial constraints.
The first option is good style.

All other considerations seem to ne correct.

However, if you delete an Operator, you should check whether it's Supervisor or not.
Then you can either let user first select new Supervisor for existing team OR delete team with supervisor.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13451094
OMC2000,

Noted, You're simply the best.

Problem now is deletion. Is it okay if we tell the user that there are still exisiting members for this particular supervisor. Deletion will not continue and we would display all the existing memebers??

Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13451212
Yes, it's a good approach. You could also display name of the group in order to let user easily find it change supervisor.
Also, recommendations for such problem resultion should be reflected in user documentation:
User's options: delete all members one by one / change group for memebers / change supervisor for group
0
 
LVL 1

Author Comment

by:lynnton
ID: 13451265
OMC2000,

I didn't get the recomendation part ? is that for the actual manual book ?

Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13451322
yes, for manual book.

I lost part of my statement, sorry. Too many parallel threads, I have to work sometimes:)
0
 
LVL 1

Author Comment

by:lynnton
ID: 13451336
OMC2000,

Noted, thanks so much for your time and patirence.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13451348
OMC2000,

Kindly check your feedback corner.

Thanks.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 13451429
Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question