tedd wrote: (and I added in some extra bits...) > You need to normalize. > > Authors should have an unique id in an authors table. The authors table > has all the specific information about authors, but not the books they > have written. > > Books should have an unique id in a books table. The books table has all > the specific information about books, but not the contributing authors. > Like the ISBN, for example - that should be unique enough for anyone... I suppose if you deal in antique books, there might not be an ISBN. > Then you connect the two tables with a Book-Author table that has only > the id's of both -- no real need for any other information. > This also has the advantage that when you come to add new books by authors already in the database, you only have to look the name up, and you can avoid duplicating authors with misspelt names, etc. You will have to allow for the case of a book with multiple authors, but that should work out fine - you just have two (or more) records in the Book-Author table to link the same book to several authors, and logic that watches out for that when you extract the data. > That way when you want to see all the books an author has written, then > you pull out all the records that has the author's id and look up each > book via the book id. > > Likewise, when you want to see all the authors who have contributed to a > book, then you pull out all records that has the book's id and look up > each author via their author id. > > Do you see how it works? > > Cheers, > > tedd > It always surprises me how many people need to have database normalisation explained to them - it seems obvious to me... (and tedd, clearly!) -- Peter Ford phone: 01580 893333 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
