Jump to content
Larry Ullman's Book Forums

Recommended Posts

I'm having difficulties understanding how a intermediary table works.

The example on page 168 makes sense and is simple to understand (the books and authors example), but when I tried to apply it to my design it didn't work out.


This is my intended design: I have a 'users' table. Each user has a course/s that he's taking, 'course_id.' The name of the course is in the 'courses' table, and the intermediary table is 'users_courses.' I want to extract the 'user_id' together with the 'course_name.'


user_id (pk)


user_id (pk)



course_id (pk)


Is this the right way to go? It doesn't make sense to me, because I can't enter multiple values for the same user in a case where he's taking more than one course since it's going to create duplicate keys.

How do I make it work?

Link to post
Share on other sites

Your design is correct, but you are incorrect in that duplicate keys will be created. For example, let's imagine the following users and courses tables:



1     Bill
2     Pam
3     Steve


1     Intro to Web Development
2     Advanced Web Development
3     Basket Weaving
4     Basketball

Now, let's say that Bill wants to take both Intro to Web Development and Basketball. Then we'd enter the following two entries into the users_courses table (where the first number is the user ID and the second is the course ID):

1     1
1     4

Even though the user ID is the same, because the combinations of the user ID and course ID are unique, we're fine. In other words, DB integrity has been preserved.


With the above design (i.e., your design), you could then run the following query to get all courses that Bill is taking:

SELECT course_name
FROM courses, users_courses, users
WHERE courses.course_id = users_courses.course_id
AND users_courses.user_id = users.user_id
AND users.user_id = 1;

Really, the important thing to note is that user_id by itself cannot be a primary key for the users_courses table. The "key" is to make a multicolumn key, which is very common.


That make sense?

  • Upvote 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...