I don't think you've explained things well. The stored employee number should be the exact employee number. If it is stored with the category, then I would assume that is the way the company set it up and that's the actual employee number. Now that's kind of dumb because the category may change and you then have one of two bad situations: The employee number has to change (and that is most likely to have serious ramifications) or it has to remain the same (in which case the category is meaningless going forward).

But if the company sets up something stupid like this, then I guess you have to store it exactly that way. I'd create a separate PK and use that as the "internal, behind-the-scenes" employee number, then I'd also create the category number field. So it would be:

ID   Employeenumber    Category
1    1-12345           1
2    2-42535           2
3    1-34545           1
4    1-34534           1

Redundant, yes, but only because the company made a dumb decision on how to set up their employee numbers. If this is the way the programmer designed it, then I'd say that's a bad decision, too, and does break normalization.

>>>Hi all,
>>>
>>>Here is the introduction to a thought I had that is hopefully interesting for you too.
>>>
>>>Database normalisation is based on certain assumptions (this list is not exhaustive):
>>>
>>>1) Each table must have a primary key: One, two or eventually even more fields that uniquely identify a record. The smaller the number of such fields, the better.
>>>
>>>2) Atomiticy: Each field decribes one and only one entity.
>>>
>>>3) The order of the records is not important.
>>>
>>>4) Entities must not be stored redundant.
>>>
>>>One consequence is that we want to avoid two or more entities to be described by one field only. And we reserve a field for each of those entities. Example:
>>>
>>>

>>>Employeenumber    Category
>>>12345             1
>>>42535             2
>>>34545             1
>>>34534             1
>>>

>>>
>>>Now consider the following case:
>>>
>>>

>>>Employeenumber
>>>1-12345
>>>2-42535
>>>1-34545
>>>1-34534
>>>

>>>
>>>We would critize the datamodel if we would encounter the above structure and were told that the employeenumber also incorporates the category. We would say that the atomiticy rule is broken.
>>>
>>>In the next case the employeenumber also incorporates the category. But moreover, the category is also stores separately. In this case, we might argue that this is a clear case of redundancy. But is this really, always, the case?
>>>
>>>

>>>Employeenumber    Category
>>>1-12345           1
>>>2-42535           2
>>>1-34545           1
>>>1-34534           1
>>>

>>>
>>>Yesterday I would, today I no longer have that opinion. How about you?
>>
>>It seems to be a redundancy, and btw primary key should be meaningless.
>
>It may well be redundant indeed. But how can we be sure about that?
>
>In my eyes, your second idea is not a prerequisite.