Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
abbyG
Starting Member
1 Post |
 Posted - 2012-10-04 : 16:17:59
|
| Greetings,I have a design problem that I am having trouble with.I'll preface with the fact that I am not real experienced with SQL design.My problem is that I have 2 tables with data (Operations and Objects) that are joined in a many-to-many relationship with a bridge or junction table (Permissions). Now I want to assign roles to permissions by creating a many-to-many relationship between the Roles table and the Permissions table, which is itself a junction table. To accomplish the many-to-many relationship between these, I have a RolesToPermissions junction table. The problem arises when I try to make the 1-to-many relation between the Permissions junction table and the RolesToPermissions junction table. SQL server will not let me make this relation.Is there any other way around this, or am I doing something wrong here.OperationsTable ObjectsTable--------------- ------------- opID(PK) objID(PK) name name \1 /1 \ / \ / \many /many Permissions (Junction table) ------------- opID(PK) objID(PK) permID(PK) ?1 ? ? <-- error when making relation ? ?many RolesToPermissions (Junction table) ------------------- roleID(PK) permID(PK) |many | | | |1 RolesTable ---------- roleID(PK) nameThanks,Abby |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-04 : 16:24:36
|
| i would have set pk of Permission table as just PermIDcan you show how you created relationships?I think it should be likePermissions(objid) -> objects(objid)Permissions(opid) -> operations(opid)RolesToPermission(permid) -> permission(permid)Rolestopermission(roleid) -> Roles (roleid)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Junction table problem