October 4, 2012 at 2:28 pm
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.
OperationsTableObjectsTable
--------------- -------------
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
October 4, 2012 at 2:48 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2012 at 11:42 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply