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.'

 

users:
user_id (pk)

 

users_courses:
user_id (pk)
course_id

 

courses:

course_id (pk)
course_name

 

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?

Share this post


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:

 

users

1     Bill
2     Pam
3     Steve

courses

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

Share this post


Link to post
Share on other sites

It does now.

So the combination of user_id and course_id is a unique index, and there's no primary key in users_courses, right?

 

By the way, why wouldn't you create a join in your query? Is it just for simplicity's sake?

Share this post


Link to post
Share on other sites

Yes, it is a unique index, and there is no primary key.

 

Also, my query is doing a join across three tables, although it may not be very obvious.

The syntax I used is an abbreviated way of performing an inner join across three tables.

Share this post


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.

Guest
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.

Loading...

×
×
  • Create New...