vendredi 2 janvier 2015

How to create a Relation between a table and a many-to-many relation table


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





Aucun commentaire:

Enregistrer un commentaire