• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

recursive table designing

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
lynnton
Asked:
lynnton
  • 14
  • 10
1 Solution
 
OMC2000Commented:
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
 
lynntonAuthor Commented:
OMC2000,

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

Thanks.
0
 
lynntonAuthor Commented:
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.

 
OMC2000Commented:
I'm creating these table to check it in work...
0
 
lynntonAuthor Commented:
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
 
OMC2000Commented:
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
 
lynntonAuthor Commented:
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
 
OMC2000Commented:
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
 
lynntonAuthor Commented:
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
 
lynntonAuthor Commented:
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
 
OMC2000Commented:
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
 
lynntonAuthor Commented:
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
 
lynntonAuthor Commented:
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
 
OMC2000Commented:
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
 
lynntonAuthor Commented:
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
 
lynntonAuthor Commented:
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
 
OMC2000Commented:
"( 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
 
lynntonAuthor Commented:
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
 
OMC2000Commented:
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
 
lynntonAuthor Commented:
OMC2000,

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

Thanks.
0
 
OMC2000Commented:
yes, for manual book.

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

Noted, thanks so much for your time and patirence.
0
 
lynntonAuthor Commented:
OMC2000,

Kindly check your feedback corner.

Thanks.
0
 
OMC2000Commented:
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now