Search the Community
Showing results for tags 'indexes'.
-
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
- 6 replies
-
- normalization
- database design
-
(and 2 more)
Tagged with: