Intermediary Table Design

[emilg1984]

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?

[HartleySan]

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?

[emilg1984]

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?

[HartleySan]

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.

[emilg1984]

Thank you HartleySan! It always surprises me how helpful your answers are. Keep up the good work!

[HartleySan]

Glad I could be of assistance.

Please keep up the good work yourself!