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

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

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

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

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