SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design problem with junction table


Design problem with junction table

Author
Message
abbyG
abbyG
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
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)
name


Thanks,
Abby
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8987 Visits: 7660
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
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14830 Visits: 12238
Abby

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



Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search