emilg1984 Posted April 18, 2014 Share Posted April 18, 2014 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? Link to comment Share on other sites More sharing options...
HartleySan Posted April 19, 2014 Share Posted April 19, 2014 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? 1 Link to comment Share on other sites More sharing options...
emilg1984 Posted April 19, 2014 Author Share Posted April 19, 2014 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? Link to comment Share on other sites More sharing options...
HartleySan Posted April 19, 2014 Share Posted April 19, 2014 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. Link to comment Share on other sites More sharing options...
emilg1984 Posted April 19, 2014 Author Share Posted April 19, 2014 Thank you HartleySan! It always surprises me how helpful your answers are. Keep up the good work! Link to comment Share on other sites More sharing options...
HartleySan Posted April 19, 2014 Share Posted April 19, 2014 Glad I could be of assistance. Please keep up the good work yourself! Link to comment Share on other sites More sharing options...
Recommended Posts