×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Organisation Chart database

Organisation Chart database

Organisation Chart database

(OP)
I am designing a database to reflect our Organisational chart.

The hierachical relationships to model are:
The lowest level is an Employee.
Many Employees can belong to the next level - Services.
Many Services can belong to the next level - Portfolios.
Many Portfolios can belong to the next level - Clusters.

All four entities are represented by their own table, with a foreign key to the level above.

Services, Portfolios and Clusters all have a manager linked to the Employee table.
The Employee table has a link to the Service Table (Service_Id) on a one to one basis.

The trouble is, for employees who are also managers at the Portfolio level, they do not have a specific Service.
For employees who are also managers at the Cluster level, they do not have a specific Portfolio or service.
For these employees, the whole realtionship between levels breaksdown.

I am tempted to include a Portfolio_Id and Cluster_id to the Employees table, but I think this breaks rules of normalisation.

Thoughts

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

RE: Organisation Chart database

Having Services link to Employee AND employee link to Services IMO is not a good idea, it should be one way or another not both.

I have not worked out all of the details of what you are doing but here are some ideas to consider.

-What about having (a) linking table(s) e.g. A Employee_Services table that is just 3 field: PK, FK_Services, FK_Employee.  Now the Employee and Services tables no longer need FKs and you have have any mix of relationships. E.g. a Service can have 1,2,3+ Employees and a Employee can be with Multiple Services.  Do this for the other tables/Employee.  Or create one linking table table (I see two different designs for this table: PK, FK_Service, FK _Employee, FK_Port, FK_Clust where only 2 of the FK_ Rows have data per record  -OR- PK, FK_1, FK_2, Table_Name1, Table_Name2 )

- "breaks rules of normalisation."  Words of wisdom:  "Normalize until it hurts, then de-normalize until it works"  e.g. On can go a little crazy trying to get a perfectly normalized database, e.g. I worked on one that the designer replaced postal ZIP codes with FKs because "Good normalization requires repeating data in a field/column to be replaced with a FK to a table"   That is going to Far IMO, I used to say he normalized the data to the 9'th level.

-BTW with the linking table you could add a start and end date to hold history information of the jobs the employee held over time, but that may be going too far.
 

Lion Crest Software Services
Anthony L. Testi
President
www.LionCrestSoftwareServices.com

 

RE: Organisation Chart database

(OP)
Thanks for the advice on "Normalize until it hurts, then de-normalize until it works". Couldn't agree more.

Your suggestion looks at setting up a many to many relationship between employees and services, with a junction table in the middle. This sounds reasonable in most oganisations, but in ours each employee can be considered to have a primary service, at least for the purpose of the application, which is downstream from the 'real' HR database.

It is looking more and more like I have to design the database to enable recursive searching through a hierachy of organisational units....it will proably get quite ugly.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

RE: Organisation Chart database

shaunk,

you should just build the hierarchy with mandatory FK's. At the portfolio level, have a field called IS_A_MANAGER which is boolean, and add a constraint allowing null foreign key values if the boolean is true.  That way you maintain referential integrity, but don't compromise the design by allowing non-managers to not have a foreign key.

Can you please post the create table statements for what you intend to do, so that we can build them locally and inspect them easily.

Regards

T

RE: Organisation Chart database

(OP)
Thankyou Thargy,

The Accountants have now become involved and turned the whole thing on its head by incorporating cost centre to cost centre relationships, in addition to the employee-manager org structure.
I will have to now go back and re-design, but will post the end result up here.
I understand what you are saying with the mandatory foreign key and the added constraint allowing nulls if the the boolean is true. I will most likely have to incorporate this into the new design.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

RE: Organisation Chart database

Well
(normally) it is better to have these kinds of changes/requirements (The accounting needs) spelled out at design time, then after the whole database is created.  

Best of luck.
 

Lion Crest Software Services
Anthony L. Testi
President
www.LionCrestSoftwareServices.com

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close