asked on

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.

OMC2000

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.

lynnton

ASKER

OMC2000,

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

Thanks.

lynnton

ASKER

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.

OMC2000

I'm creating these table to check it in work...

lynnton

ASKER

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.

ASKER CERTIFIED SOLUTION

OMC2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lynnton

ASKER

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.

OMC2000

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)

lynnton

ASKER

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.

lynnton

ASKER

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.

OMC2000

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:)

lynnton

ASKER

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.

lynnton

ASKER

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.

OMC2000

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.

lynnton

ASKER

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.

lynnton

ASKER

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.

OMC2000

"( 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.

lynnton

ASKER

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.

OMC2000

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

lynnton

ASKER

OMC2000,

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

Thanks.

OMC2000

yes, for manual book.

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

lynnton

ASKER

OMC2000,

Noted, thanks so much for your time and patirence.

lynnton

ASKER

OMC2000,

Kindly check your feedback corner.

Thanks.

OMC2000

Thanks