Search the Community
Showing results for tags 'normalization'.
I have a question about the database design for my project. Originally I was going to have separate tables for Managers, Owners, Players, etc... since they all require different information. This design created a problem on how to add phone numbers. In order to do this, I was going to have to create separate tables for Managers_phone, Owner_phone, Player_phone since you couldn't have a foreign key in one phone table relate to different tables. Could you just create one table for People, with all of the information that they have in common such as first name, last name, address, etc..., and link phone table to this table, and then have seperate Manager, Owner, player tables to store the information that is unique to each? Also, I am planning on just storing the zip code on the people table, and then have a separate table that matches each zip code to the proper city. Is this fairly common, or do you usually store City, State, and zip in the same table?
How does This table allow for multiple authors for one book? if it doesn't pleae tell me how to implement it. (page 176, chapter 6 - normalization) PUBLISHERS publisher id name address1 address2 city state zip BOOKS book_id publisher ID title BOOKS AUTHORS book_id Author_id AUTHORS author_id author I'm not sure how everything ties together,...please explain it more. why doesn't BOOKS reference to author? like if you were searching it...shouldn't the author be a foreign key so it's in the books table?? also, why are all the ids with an underscore except publisher id? I'm not sure if that matters.
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
I have a MySQL database model where I have different types of users, each having common field names, like 'username', 'password', 'first_name', 'last_name', etc. etc. I've designed it so that these common fields are stored in a 'base' table called 'users' and specific fields that apply to each type of user are stored in derived 'sub-tables' for each type of user. Here is what it looks like: I know that this is a better design than having the 'common' fields stored in every 'user' table. The problem I'm having is I'm trying to figure out how I'm going insert a new user. What would be the best way to handle this? Initially, I'm thinking I could do an insert like this (let's say I wanted to insert a student): <?php // Begin Transaction // Insert the common data into the 'users' table // Get the last inserted ID // Insert the student-specific data into the 'students' table along with the last inserted ID // Commit // If problem occured // Rollback ?> But that just seems like a very crappy way to do it. I would like to have it done in one swift statement. So I haven't had much luck finding clear solutions online, but I recall one person mentioning the use of updatable views for each subtype, where the view would perform an inner join on the subtype table and the base table, and you could insert and update using the single view. I have tried to create a view but keep getting the error: #1356 - View '[view name]' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. Where [view name] is the name of my view. I get this error even when just selecting one column from one table and I know the names are right. Would the 'view' approach be the best way to go, assuming I can figure out how to get it to work? Is there a better way than what I've mentioned so far? Much thanks, Zane EDIT: Oh, and the PK 'user_id' is an auto-incremented INT in the users table. EDIT 2: Got my view to work. Turns out I had to specify the SQL SECURITY line as INVOKER instead of the default DEFINER. Going to try to see if I can perform inserts and updates on this view...