Newbie normalization question (or, "My brain hurts")

[davidfox]

I would imagine that reading through someone else's normalization problems is as exciting as watching paint dry.

But I have seen some brilliant answers presented here so I'll give it a shot. I'll try to be brief and concise. I'm hoping someone can read through this and tell me if I'm on the right track.



I have a table full of employees. A few of these employees are supervisors. I was going to add a field called Supervisor to identify who each employee's supervisor is. I thought about just entering the Supervisor's SSN into that field. (SSN is the key field)

But every time a supervisor is promoted and replaced (quite often), every employee's Supervisor field would have to be edited. Sounds like I ought to create a seperate table with a list of supervisors, so I can replace the SSN just once.

I THINK this new table would have two fields, let's call them CrewCode and SupervisorSSN. So there might be, say, 15 employees in a particular supervisor's "crew", and all these employees' "CrewCode"s will point to their supervisor in this new table. When the supervisor is replaced, you just type in the new guy's SSN once.

Sound correct so far?

Here's where I am overcomplicating things for myself. I don't want it to be necessary for someone to arbitrarily create unique "CrewCode"s, because there are hundreds of Crews in this company but also because it feels wrong to do this arbitrarily when we already have data in other fields to uniquely identify the employee's crew.

Consider this:

The employee record has the following fields to identify which crew they work in. (I'll provide some possible values for these fields, to help put things into context.)

Region = {SE | E | NE}
Division= {Sawmill | Woodlands}
Site = {Atlanta | Charlotte | Axton | Foxboro}
Department = {Sawmill | Planermill | Treeplanting | Harvesting | Trucking | SupportStaff}
Crew = {1 | 2 | 3 | 4}

So if I string these values together, employee "John Smith" might work on a crew which is uniquely identified as:

SE.Woodlands.Atlanta.Treeplanting.2

No other crew in the company has that unique string of characters. (I would add the dots myself for readability.)

So my first question is, am I on the right track by trying to link employees with their supervisors using a table like:

SE.Woodlands.Atlanta.Treeplanting.2 593-88-6958
SE.Woodlands.Charlotte.Planermill.4 461-87-3772
NE.Sawmill.Foxboro.Sawmill.3 569-46-2674

That seems like a more elegant solution than arbitrarily assigning a CrewCodes of "00001", "00002", etc.


Second question: when the time comes to actually look up an employee's supervisor, the two tables would have to be JOINed in some manner. Is it possible (and advisable) to perform a JOIN using a collection of FIVE fields in the employee table? Or would I have to combine then into a new single field first, then JOIN on that field? I'd really like to avoid doing that.

Any help would be so very much appreciated!

 

[Pat Hartman]

But every time a supervisor is promoted and replaced (quite often), every employee's Supervisor field would have to be edited.

This isn't as onerous as it sounds. All it takes is an update query that selects people supervised by x and changes x to y.

What you are proposing is much more complex and like all complex solutions could tumble like a house of cards if the slightest thing changes as far as the company's structure goes. I would stick with the simple - point to the specific employee who is supervisor method.

 

[davidfox]

Thanks for the reply.

I was hoping you were going to say that.