Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Design problem with junction table Expand / Collapse
Posted Thursday, October 4, 2012 2:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2012 2:32 PM
Points: 1, Visits: 2
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)

? <-- error when making relation

RolesToPermissions (Junction table)

Post #1368716
Posted Thursday, October 4, 2012 2:48 PM



Group: General Forum Members
Last Login: Wednesday, September 23, 2015 3:34 PM
Points: 5,467, Visits: 7,660
Hey Abby,

You're new here so you're probably just not sure of how to present your issue. If you take a look at the first link in my signature below, it'll show you how to setup data so we can help you with tested code instead of trying to rebuild it ourselves and then guessing at the correct results.

What you initially asked for doesn't sound too hard, but I really didn't follow the details once you started breaking them down. Can you take a look at that and setup the question with that information? Once we have that, a number of people will probably jump in to assist.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1368730
Posted Friday, October 5, 2012 11:42 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 11:18 AM
Points: 9,108, Visits: 11,016

your notation is rather strange, I can't follow it, so I can't see what's giving an error.

However, your text seems to indicate what you are trying to do and it is pretty straightforward. Some SQL is given below which creates the tables and the relationships between them, but no columns other than columns involved in primary or foreign keys (you'll need to add those, where they should go is commented). I've left out indexes other than needed to support primary key. I've also left out the surrogate key permID because it seems a superfluous complication; but it's easy to add if you must.

Of course because I'm working from a brief text description rather than something more specific (as indicated by Craig in his message above) this may not actually be what you want.

create table operation (
opID int primary key
-- ,other columns
create table object (
objID int primary key
-- ,other columns
create table permission (
objID int not null references object(objID)
,opID int not null references operation(opID)
-- ,other columns if any
,primary key(objID,opID)
create table role (
roleID int primary key
-- ,other columns
create table rolepermission (
roleID int not null references role(roleID)
,objID int not null
,opID int not null
-- ,other columns if any
,primary key (roleID,objID,opID)
,foreign key (objID,opID) references permission(objID,opID)

-- drop table rolepermission, permission, object, operation, role

Post #1369224
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse