Organisation Chart database

[shaunk]

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

 

[MrDataGuy]

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

http://www.LionCrestSoftwareServices.com

 

[shaunk]

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

 

[Thargy]

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

 

[shaunk]

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

 

[MrDataGuy]

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

http://www.LionCrestSoftwareServices.com