frontline employees becoming managers - data normalisation

[etk]

Suppose you had three tables in an employee database, 1 for a frontline employee, 1 for their direct manager, and 1 for their manager's manager.

If a frontline employee then becomes a manager and is in both employee and manager tables, does this violate normalization if the manger id is a foreign key on the employee table? If so, what is a solution becuase I need to visualize these managers as frontline employees for historical calculations across various KPI as a team perspective.

 

[boblarson]

The answer is that I would not have 3 tables but would have had only one to begin with. Are there fields in the other two that necessitate it being in 3 tables instead of one? You can use a self referencing field to store each employee's manager, including their manager and you can have a field that is a flag that they are a manager.

 

[ButtonMoon]

Suppose you had three tables in an employee database, 1 for a frontline employee, 1 for their direct manager, and 1 for their manager's manager.

If a frontline employee then becomes a manager and is in both employee and manager tables, does this violate normalization if the manger id is a foreign key on the employee table?

No. None of the conventional normal forms is concerned with the problem of tables that may "overlap" (may contain some or all of the same types of data). The Principle of Orthogonal Design (POD) is a design principle that attempts to fix that by formalising the problem. POD is discussed at some length in the book Database Design and Relational Theory by Chris Date and in Date on Database by the same author.

To many people I suppose it seems "obvious" that tables generally ought not to contain the same or overlapping sets of data (I remember being told as much 20 years ago when I first started developing on Sybase). Your question is still a good one though because algorithms and CASE software in use today for generating normalized database designs will still manage to create or fail to detect designs that violate the POD.

If so, what is a solution becuase I need to visualize these managers as frontline employees for historical calculations across various KPI as a team perspective.

Make supertype and subtype versions of your tables that contain the attributes specific to one type of employee or another. Take attributes common to more than one type of employee and push them "up" the hierarchy so they appear in as few tables as possible. The employee to manager relationship may be best represented as a "joining" table.

Hope this helps.

 

[Lightwave]

Bob's and Button's solutions are similar.

In both cases you do not at any cost create duplicate tables containing essentially the same fieldname concerning information on employees. That would lead to problems in the future. What happens when an employee becomes a manager? A - you would have to move records between tables messy and you still wouldn't have the relationship from one to the other.

You simply create additional fields that store the relationship. In bob's example that field is in the same table, in buttons' solution the field is located within a separate table. My instinct would be to go for a separate table.

For example the additional table might be called Manager and employee and contain three columns

Fieldname 1: PKID - simple identity
Fieldname 2: ManagerPKID
Fieldname 3: EmployeePKID

If you wanted to know how many employees a manager was in charge of you would simply filter on field 2 for the identity of the individual you wish to identify. O entries would mean 0 charges. 4 entries 4 charges. Not only that the value of those IDs would allow you to identify who those employees were.

Similarly if you wanted to know the manager of an employee you would filter on Field3 to identify the manager.

This is sometimes referred to as a joining table or a junction table. The interesting thing is the way in which you are storing the relationship between the individual records within the same table. So in essence it is a recursive self join.

Mull it over in your head. It is not a NEW type of join it is a clever twist of thought about the use of one of the only two types of join - namely one to many.(as against one to one)

The importance of junction tables cannot be understimated and it is vital to your progress that you try and understand the idea.

 

[Mihail]

May I ask something, please ?

Think to the army management schema:
Barack Obama is the big boss (Manager_1)
His direct employes are also managers for other employes that are also mangers for other employes that are also.... until we pick Jon Doe that is not a manager.
Is there a way to find all employes for any manager, starting with Obama and ending with Jon Doe ?
Or, starting with Jon Doe, is there a way to find the thread until Obama ?

I can imagine a solution based on a recursive VBA function.
But I'm wonder if exist something similar based exclusive on SQL language.

Hope you get the idea even if my English is as is.
Thank you.

 

[Lightwave]

I suspect that the Access version of SQL (don't know this for sure) doesn't support recursion. SQL Server 2005 I read does support something called Common Table expressions that allow for recursion.

Can SQL support recursion? LINK

However apparently you can obtain hierachies using SQL without necessarily resorting to recursion.

Determining Hierachy using SQL without the requirement for recursion

I suspect this second solution might be possible using SQL in Access.
(CAUTION don't know for sure)

My first thought like you Mihail was a VBA loop using recursion with a similar structure to below.

Let us take an organisation with 14 employees organised as follows with this information being stored in one table with field 1 being manager and field 2 minion/underling.

1 manages 2
1 manages 3
2 manages 4
2 manages 5
4 manages 8
4 manages 9
5 manages 10
3 manages 6
6 manages 11
3 manages 7
7 manages 12
12 manages 14
7 manages 13

It would be useful for anyone following along to draw the structure out as a pyramid.

Certainly the definition of the top boss is easy - anyone that is in the first field but not the second field and the definition of a John Doe easy they appear in field 2 but not field 1.

Middle management is anyone that exists in both fields.

To find out how many and who one individual manager manages is where I think you would have to have the recursive VBA or SQL. You would effectively filter on the first field based on the ID of the individual you want to determine minions for.

Of the subsequent set(Lets call it the first set) you would wish to recursively filter the whole set based on the second column of the first set. You would continue recursively filtering on the second field name of the subset of the former set until you not longer got unique pairs. Add all the unique pairs of all those subsets and you have how many employees that individual manages. ie. the second column of the combined set of all the filtered first sets would give you all the people that are in some way managed by the individual you originally searched on.

I thought this all up after having drawn out a pyramid - then did a search on google and found the links I moved to the start of my reply.

Maybe should have done the search first!

Good question though Mihail