CalebCurry Posted July 24, 2012 Share Posted July 24, 2012 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. Link to comment Share on other sites More sharing options...
HartleySan Posted July 24, 2012 Share Posted July 24, 2012 The BOOKS_AUTHORS table is the table that maintains the many-to-many relationships and foreign keys. For example, imagine the following books and IDs: BOOKS 1 Rocket Science for Dummies 2 DBs Are Fun AUTHORS 1 James Brown 2 Billy Kid 3 Arthur Smith Now, let's assume that James Brown and Billy Kid co-authored Rocket Science for Dummies and Arthur Smith was the only author of DBs Are Fun. If that's the case, then your BOOKS_AUTHORS table would look like the following: BOOKS_AUTHORS 1 1 1 2 2 3 In other words, the Rocket Science for Dummies book (with ID 1) would be in the BOOKS_AUTHORS table twice, once for each author (with IDs 1 and 2). In order to get all the necessary info for various queries, you would have to use joins. Make sense? 1 Link to comment Share on other sites More sharing options...
CalebCurry Posted July 24, 2012 Author Share Posted July 24, 2012 Yes, Thank you so much!!! I want to make an indepth database for my books and I was so confused about it. Is there anything else I should do to the database for easier use? Link to comment Share on other sites More sharing options...
HartleySan Posted July 24, 2012 Share Posted July 24, 2012 I think "easy" is such a relative term (no pun intended). When most people (including myself) first start learning DB design, their first instinct is to put everything in one table (and there are some NoSQL experts who might still argue that point). However, as we all know, in order to avoid redundancy and all sorts of DB inconsistencies, it's essential that we separate our data into separate tables. To that end, all many-to-many relations should be broken up. If you keep that one rule in mind and use a little common sense, I think you'll more or less be okay. Of course, a lot of experience pays off too, so I would recommend checking back here occasionally if you have any questions. There are people far more knowledgeable than me on these forums that can help. Good luck with your project. Link to comment Share on other sites More sharing options...
Recommended Posts