Search the Community

Hi Larry, I am experimenting with foreign keys as per chapter 6 and am 'learning'. I thought that I would try to add that feature to a website that has a 'galleries' table and a 'works_of_art' table (as well as many other tables). One gallery can have lots of works of art associated with it but a work of art can only 'belong' to one gallery - i.e., a one to many relationship. In my experimentation, I assigned a foreign key constraint in the works_of_art table to the primary key of the galleries table using CASCADE as I thought it would be nice that if a gallery were deleted all its associated works of art would also automatically get deleted. But during my experimentation and 'playing around', I discovered that you cannot use a TRUNCATE table on the galleries table even if the works of art table is empty. As I use the TRUNCATE feature in your database restore routine (I use it and the associated database backup routine in several websites), I'd appreciate your thoughts. Thanks and Cheers from Oz.

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