Many to Many Relationship

  • Thread starter Thread starter tryfanman
  • Start date Start date
T

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!
 
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.
 
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.
 
Back
Top