junction table 3

[stucker79]

I have a many to many relationship which I have designed as two tables each with a one to many relationship to a junction table (with the id fields from each of the other two tables). When I do an insert to the two "regular" tables, what is the best way to get the id values into the junction table? just another insert statement, triggers, something else? I've never used triggers before so i'm not sure if they are appropriate.

 

[checkai]

I would use an insert stored procedure, first checking to make sure your values are in both tables, then insert the join values.

 

[vongrunt]

Junction tables are often about explicit events (assign this to that), so I don't think this is a good task for trigger. Anyway... can you give us exact example?

 

[stucker79]

For instance, I have a table for users, and a table for groups. A user can belong to more than one group, and a group can have multiple users in it. So I made a UserGroup table that contains the following fields: UserGroupID (pkey), UserID (fkey), GroupID (fkey). Then I made a one to many relationship from users to UserGroup and from Groups to UserGroup. So when a new user is added to the user table, an associated group(s) is chosen, and then I need to send each userid/groupid pair to the UserGroup table. Hopefully I set that up in a good way!

 

[donutman]

I agree with vongrunt and checkai. Create a SP that handles the insert into the User table. Get the value of the UserID from scope_Identity and insert it into the UserGroup table for each Group associated with the user.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

and ditch the UserGroupID

the primary key of the junction table should be (UserID, GroupID)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

Give that man a star.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[stucker79]

thanks i wasn't sure if i needed another primary key for the usergroup table. how do i designate both of those fields as the primary key for the usergroup table?

 

[donutman]

Select them both in the designer window. Right click and set primary key.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]