Mysql – Now to normalize table when position in group uniquely identifies entity

You'll probably end up with something looking a little like this: This may be a little more involved than you first expected, but I'll take you through the whys and hows of it all ( doing this is generally much easier than explaining it, by the way ), starting with identifying objects. Using "Noun-Verb" analysis, which is a fairly common OOAD technique, we start picking apart your requirements:

Right away, it becomes evident that at least Employee, Position / Job and Skill are needed. Further along, a need for Department and Skill Group emerges, then finally Skill Level. From here, things like Employee Surname are noted as well.

Some liberties are then taken here ( which is not abnormal in any way, considering the quality of the list of requirements you'd normally be extracting from a client ), but the noun list to be used for the general structure, in no particular order, is as follows:

Department ( D )

Department Code ( DC )

Department Name ( DN )

Position ( P )

Position Code ( PC )

Position Title ( PT )

Skill Group ( G )

Skill Group Code ( GC )

Skill Group Name ( GN )

Skill ( S )

Skill Code ( SC )

Skill Name ( SN )

Employee ( E )

Employee Code ( EC )

Employee First Name ( EF )

Employee Surname ( EL )

Level ( L )

Level Code ( LC )

Level Name ( LN )

Using these nouns, on a relatively simple level, relationships between them can be established using "is-a / has-a," that is, "Noun A is a Noun B" or "Noun A has a Noun B." This is particularly useful for classifying our nouns into two categories, being an "attribute", an atomic entity, later called a column, or a "container," which is a collection, later called a table. During the first round of this "is-a / has-a," the goal is generally to simplify the list, effectively removing the attributes from the picture, grouping them to their containers.

The naming convention used above goes a fairly long way to making this process simple. For instance, an Employee ( E ) has an Employee First Name ( EF ) and an Employee Last Name ( EL ), forming one-to-one relationships between Employee ( E ) and the Employee First Name ( EF ) and Employee Last Name ( EL ) entities. In this way, Employee ( E ) can be classified as a container, whereas Employee First Name ( EF ) and Employee Last Name ( EL ) become attributes. Focusing on this reduction of nouns via one-to-one relationships, we can reduce the list above to the following, where the notation ( A: B, C ) means that entity A is a "container" with "attributes" B and C. It follows that:

Department ( D: DC, DN )

Position ( P: PC, PT )

Skill Group ( G: GC, GN )

Skill ( S: SC, SN )

Employee ( E: EC, EF, EL )

Level ( L: LC, LN )

With this reduction, the one-to-many relationships can now be considered. A Department ( D ) may have any ( a pluralized has a ) number of Positions ( P ) available, implicitly indicating a one-to-many relationship. Extending the above notation, in addition to the attribute list, we can now add "compound" attributes. More simply, a compound attribute is basically a container treated as an attribute. This can be written as ( P: PC, PT, ( D: DC, DN ) ), which begins to highlight the places where foreign keys will materialize. Similarly, Skill Group ( G ) may refer to a number of differing Skills ( S ).

( P: PC, PT, ( D: DC, DN ) )

( S: SC, SN, ( G: GC, GN ) )

The many-to-many relationships can then be addressed. Position ( P ) may require any number of Skills ( S ), each with it's own Skill Level ( L ). Conversely, a specific Skill ( S ) can be required for any number of Positions ( P ), at any particular Skill Level ( L ). These associations manifest as a ( is a ) Position Skill ( PS ), effectively a "surrogate" container, having a compound attribute of Skill Level ( L ). To use the phrasing above, each of these newly established Position Skills ( PS ) has a required Skill Level ( L ). To maintain this many-to-one relationship between Position Skill ( PS ) and Level ( L ), a unique key is to be placed on the foreign keys Position ( P ) and Skill ( S ).

( PS: ( { U₁ }( P: PC, PT, ( D: DC, DN ) ), { U₁ }( S: SC, SN, ( G: GC, GN ) ), ( L: LC, LN ) )

A quick note: The { U_n } is used to indicate both attributes ( themselves containers ) are part of a single unique key. If the attributes were independently unique, we could use { U₁ } and { U₂ }.

Next, an Employee ( E ) can occupy any number of Positions ( P ), indicating that there is a Employee Position noun to be added to our list. This particular relationship manifests over some period of time, thus having ( has a ) Start Date ( T1 ) and End Date ( T2 ). However, there is no way to assert that a normally part-time Employee ( E ) could not be serving under two separate Positions ( P ) in a full-time manner simultaneously in the interim before a new hire, nor that a previous Employee ( E ) could be re-hired or otherwise return to a Position ( P ) previously held by that Employee ( E ). This dilemma results in a true many-to-many relationship, with Start Date ( T1 ) and End Date ( T2 ) as attributes. As Start Date ( T1 ) and End Date ( T2 ) are not functionally dependent on anything other than the relationship between Employee ( E ) and Position ( P ), this structure is not in violation of 3NF.

( EP: ( E: EC, EF, EL ), ( P: PC, PT, ( D: DC, DN ) ), T1, T2 )

The final piece comes in the form of the relationship between an Employee's ( E ) specific term of employment, Employee Position ( EP ), the relevant Position Skills ( PS ) and the quality at which those specific Position Skills ( PS ) are realized, or simply, the achieved Level ( L ). Much like the relationship established between Position ( P ) and Skill ( S ), there is a Employee Position Position Skill ( EPPS ) which has a compound attribute of Level ( L ) in a many-to-one relationship. This again requires a unique key, leaving our noun list ( now expanded ) in the following state:

( EPPS:

    { U₂ }( EP:

        ( E: EC, EF, EL ),

        ( P: PC, PT, ( D:

            DC, DN ) ),

        T1, T2 ),

    { U₂ }( PS:

        ( { U₁ }( P:

            PC, PT, ( D:

                DC, DN ) ),

        { U₁ }( S:

            SC, SN, ( G:

                GC, GN ) ),

        ( L:

            LC, LN ) ),

    ( L:

        LC, LN ) )

That, of course, is a bit on the crazy side to read, so we extract the definitions we've reduced the problem to in an actionable way. Here, I've added additional unique keys simply for the purpose of completeness. Note that there are no soon-to-be tables which contain more than one independently unique key, so { U₁ } is used throughout, per table:

( D: { U₁ }DC, DN )

( P: { U₁ }D, { U₁ }PC, PN )

( G: { U₁ }GC, GN )

( S: { U₁ }G, { U₁ }SC, SN )

( L: { U₁ }LC, LN )

( PS: { U₁ }P, { U₁ }S, L )

( E: { U₁ }EC, EF, EL )

( EP: { U₁ }E, { U₁ }P, T1, T2 }

( EPPS: { U₁ }EP, { U₁ }PS, L }

Both designs are technically 3NF, depending on your data population.

What a lot of people who have only a cursory understanding of formal normalization theory don't understand is that redundancy in foreign keys isn't really redundancy by definition. Transitive dependencies and partial dependencies apply to the way non-key attributes relate to key attributes.

If your composite keys happen to contain (or even consist of) foreign keys then whether your foreign key has the look of being denormalized isn't relevant. (i.e. Design 1 isn't redundant in a way which is significant to the formal definition of normal forms.)

The practical issue comes down to what the values of your keys are. If you have natural keys and the Part key consists of IDObject=123 and IDPart=1 - i.e. your IDPart values are natural number sequences which are only unique within the context of a given IDObject value, then you need to use design 1 (weak entities).

However, what a lot of people do for good (and less good) reasons is apply surrogate primary keys to every or nearly every table. I'm not going to get into whether or why to use surrogate keys. That is a holy war for another question. However, if you happen to have table-wide unique (e.g. IDENTITY) values for IDPart, then your primary key for Part is more complex than it needs to be using design 1. In that case, there is no need for IDObject in the PK of Part because the table already has a unique key just using IDPart. In such a case, design 2 is sufficient and simpler, therefore better, as a rule of thumb.

In practice, I would say weak entities are much more common in logical models than in physical models, if for no other reason than that most physical models tend to use surrogate primary keys.