Search the Community

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?

HI, Hope someone can clarify for me. I'm refering to the examples used in Chapeter 6 regarding the movie database. I have to build a database very similar. Currently all the data is stored in an excel spreadsheet. I have one column for actors (multiple values each cell) which contains all the actors names (groan not separated by last and first names), and the names are sperated by pipes. I also have a director column (1 value each cell), studio column (1 value each cell) and genre (multiple values each cell) column which also uses pipes as a separator. I realize that I have to create separate tables. In the example given the movie-actors table has two fields, movie_id and actor_id. Wouldn't that end up violating the 2NF compliancy rule since the movie_id would have to be in multiple records with each actor? In other words movie_id 1001 has 4 actors. Wouldn't that require 4 rows defining each actor? Help Steve

Hi, I'm really enjoying working through this book; I'm currently on Chapter 7. However, I have a question about database normalization from Chapter 6. I want to create a database of musical chords; for simplicity's sake, I'm limiting the chord types to the four basic triads and their inversions for now. The different types of data I'm including are the letter name (C, Db, F#, etc), the chord quality (Major, minor, Augmented and diminished), and the inversion (root position, 1st inversion and 2nd inversion). So my database would look something like this: C Major Root Position C Major 1st Inversion C Major 2nd Inversion C minor Root Position C minor 1st Inversion C minor 2nd Inversion C Augmented Root Position ... ... C diminished Root Position ... ... C# Major Root Position ... ... etc. x9 Db Major Root Position ... ... etc. x9 As you can see, each column has a lot of repetition (there are 252 triads in the database), but since these are definitions, none of the data is likely to change at any point. My question is, in order to normalize this database, would I want to make several tables, like the following: database name: chords table 1: letter_names column 1: letter_id (primary key) column 2: letter_name table 2: quality column 1: quality_id (primary key) column 2: quality table 3: inversions column 1: inversion_id (primary key) column 2: inversion table 4: triads column 1: triad_id (primary key) column 2: letter_id (foreign key) column 3: quality_id (foreign key) column 4: inversion_id (foreign key) where table 4 cross-references the other 3 tables? Or would one table suffice, similar to table 4, but without the extra ID columns? In other words, a triads table with triad_id (primary key), letter_name, quality, and inversion as its four columns, and all the data in that single table? What I'm actually confused about is the section on indexes. The book says generally one shouldn't index columns that have a very limited set of values (quality only has 4 possible values that repeat for different triads, inversion has 3 values, and letter_name has 21--higher than the others but still relatively limited). However, it also says that columns that are frequently used in WHERE or ORDER BY queries should be indexed, and I want to be able to display the triads database in several different ways and select particular groups of chords from the larger database. Also, the section before says columns that repeat a lot of data should be given their own table in order to adhere to normalization. What would the best practice be in this case? Also, I think I may not have reached this point in the book yet, but assuming I go with the 4-table structure, table 4 ends up with a bunch of empty columns--is it supposed to be like that, and I get my values by cross-referencing the other tables? Or do I have to get the values from the other three tables into table 4 somehow? Thanks. Jake