Many to Many Relationship

[tryfanman]

Hi,

I understand that the primary key of a joining table in a many to many
relationship is the unique combination of the two foreign keys of the
tables being joined. I also understand that this good because it
prevents duplicate entries in the joining table, but what if
duplicates are required?

To illustrate let me present an example: Lets say I would like to
build a database to record employees which train in certain skills
within an organisation/business. Since many employees and train in
many skills I would use a joining table to capture this with the
primary key set as mentioned above. However, in my example the
employees need to retrain their skills once a year or so, and this
needs to be captured in the database while maintaining a record of
when they're trained in that skill in the past. I would've thought
the easiest way to do this would be to have duplicate combination of
the Employee/Skill relationship, but the primary key in the joining
table will stop this.

So how is this captured in a good database design? I don't think this
a unique case since I would assume this situation would arise fairly
frequently. although I may be wrong, and I could be looking at it
from completely the wrong angle.

Kinda hope this makes sense....? I'm happy to elaborate further if
required.

Thanks!

 

[Allen Browne]

It's fine to use a AutoNumber as the primary key of the junction table.

As you said, the multi-field key is only useful when you want to prevent
duplicates. There are lots of scenarios where duplicate foreign key
combinations are valid, particularly those that are date limited.

 

[tryfanman]

OK. As usual the answer is straight fwd.

Thanks

 

[Pat Hartman \(MVP\)]

To maintain uniqueness, add a third field to the multi-field key/index. I
tend to use natural keys as the primary key for junction tables UNLESS the
table has children of its own. In that case, I use an autonumber primary
key and create a unique index to enforce the business rules. I am a firm
believer in using declarative referential integrity to enforce business
rules. The reason for switching from a compound natural key to an
autonumber is primarily to facilitate the use of combo boxes which only work
correctly with a single unique field.