See normalization... You don't need to do this but it is considered good practice for many reasons
On 2/9/09 12:04 PM, "Peter Brawley" <peter.braw...@earthlink.net> wrote: > PJ > >> Why do I need a third table? > > The clue is "author (could be 2 or more)" in your books column list. It > is incorrect to store two author names or IDs in one column. The > relationship is 1:many. So you need a bridge table: > > books(id PK, etc...) > authors(id PK, etc...) > books_authors(id PK, bid references books(id),...,aid references > authors(id), listed_order smallint, etc...) > > Now one book with multiple authors has one books_authors row for each of > its authors, and you retrieve book & author info with a simple join. > > PB > > ----- > > PJ wrote: >> 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. >>> >>> >>> >> >> >> >> ------------------------------------------------------------------------ >> >> >> Internal Virus Database is out of date. >> Checked by AVG - http://www.avg.com >> Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 >> 7:08 AM >> >> ------------------------- Olaf Stein DBA Battelle Center for Mathematical Medicine Nationwide Children's Hospital, The Research Institute 700 Children's Drive 43205 Columbus, OH phone: 1-614-355-5685 cell: 1-614-843-0432 email: olaf.st...@nationwidechildrens.org ³I consider that the golden rule requires that if I like a program I must share it with other people who like it.² Richard M. Stallman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
