I am buildng a new database inside an Sql server 2008 r2.I have the following tables & business rules:-

  • Skill that have predefined levels assigned to it, such as junior, senior, manager.
  • Staff will be assigned to certain skill’s level.

For example inside ym system i will have such an info; staff A is connected to a skill named “Sql server 20082” through Senior level.

Currently I have set the relationship between the tables as follow:-

enter image description here

But I have this concern, currently I am storing the relation between Skill & level though a many-to-many table (Skilllevel), and I am storing the relation between staff and skill though a many-to-many table (SkillLevelStaff). But on the skilllevelstaff table I am storing the same relation skillId+LevelID , which already defined inside the SkillLevel table. So can anyone advice on this please? should i for example create another system generated ID for the SkillLevel table and setit as the PK instead of the SkillID+LevelID? Thanks


In general I see nothing wrong with a many-to-many-to-many table where you have all three IDs. In this case however, you have additional columns on the SkillLevel table meaning that the Skill+Level pair has it's own unique properties. Given that I would go ahead and create an additional SkillLevelID column and use it in your SkillLevelStaff table.

Whether or not you use the SkillLevelID as the primary key is a bit trickier. Honestly in my opinion, in the end it doesn't matter. You will need at least a unique key on the SkillLevelID if you are using foreign keys and you will want at least a unique key on the SkillID + LevelID pair in order to enforce the business rule that the combination is unique. It won't really matter which one is the primary key. The clustered index on the other hand will matter. If you do most of your queries joined to the SkillLevelStaff table then it might be a good idea to use the surrogate key (SkillLevelID) and it will certainly avoid fragmentation. On the other hand something like SkillLevel probably doesn't get updated frequently so the amount of fragmentation caused by using the natural key (SkillID + LevelID) is probably going to be minimal. It will however be better if the majority of your queries use SkillID or SkillID + LevelID as part of the WHERE clause (or joining to those tables). You will probably want to do some experimentation there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.