Olaf Stein wrote: > Just about the authors > > You need a separate table for them and then an table linking authors and > books. > > You lose me here... :-( Why do I need a third table? I may have 2 or three books with 3 authors, quite a few with 2 authors and many with just 1 author. I can't see creating an extra table for every book that has more than 1 author... ? ? ? ? And wouldn't it be the same thing for the categories? Isn't the relationship between the author field in the books table and the authors table done by an foreign key? > So you have table books, authors and rel_books_authors where rel_books > authors has 3 entries for a book with 3 authors just using the book id and > the author is's > > Olaf > > > On 2/9/09 10:25 AM, "PJ" <af.gour...@videotron.ca> wrote: > > >> being a newbie to mysql, I'm a little confused about how to deal with >> the following: >> I am creating a database of books and have come down to this - table for >> books (books) including fields for >> id (primary key, auto-incr.) >> title >> author (could be 2 or more) >> category (could be several - eg. youth, fiction, comics, and history, >> for same book) >> lang (eng, french, spanish, or german) >> descr (short summary, if avail.) >> comment (review(s)) >> publisher >> pub_link (publisher's web addr. if avail.) >> bk_cover (image, if avail.) >> publish_date >> ISBN >> buy_link (if avail.) >> >> The other tables would be authors, categories, and buy_links. >> >> My problem is how to deal with several authors, categories, and links to >> sellers. >> What do I enter in the author field when I have several authors? Do I >> set up references to the author's name(s) in another table, like (3, 7, >> 23) each number representing the name of an author in another table? >> That is, .e.g. a number_id is entered in the author field in the books >> table; in the author table, I enter John Smith in the author_id field in >> the author table? Is the field for the author_id a foreign key? >> Same question for categories... >> buy_link - there are not many, but generally include an image and >> related info to be shown next to the books info (here, I suppose, just a >> few numbers will suffice to reference the table containing the >> information) and this field (buy_link_id) would be a foreign key (?) >> referencing the buy_link table? >> So, in a query (search) I would be doing joins from the books table to >> the author, category, and buy_link tables using foreign keys? >> >> But how do I deal with a search for a book by author? If there are, say, >> 3 authors (Red, White, and Blue) and the search is for White, the >> display should probably be formulated with a php function to display the >> book listing by book title (the standard display which includes all the >> relevant information about the book). So the search should first find >> the name of the author in the authors table and if it exists, then the >> author_id should reveal the ids for the relevant books and then these >> should be printed (or echoed) in the output. Gets kind of complicated >> doesn't it? >> >> Or would it all be simpler to just enter all the authors' names in the >> author field and then do a search for an author within the fields as a >> string? I suspect this would be rather slow. Also, same for categories >> and sellers since the whole database has to be searched? >> >> Am I on the right track? TIA >> >> > > > > ----------------------------------------- Confidentiality Notice: > The following mail message, including any attachments, is for the > sole use of the intended recipient(s) and may contain confidential > and privileged information. The recipient is responsible to > maintain the confidentiality of this information and to use the > information only for authorized purposes. If you are not the > intended recipient (or authorized to receive information for the > intended recipient), you are hereby notified that any review, use, > disclosure, distribution, copying, printing, or action taken in > reliance on the contents of this e-mail is strictly prohibited. If > you have received this communication in error, please notify us > immediately by reply e-mail and destroy all copies of the original > message. Thank you. > >
-- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org