Book table: book id : 1, name: some book name
Author table Author id: 1, name: author 1 Author id: 2, name: author 2 Rel_author_book table: Book id: 1, author id: 1 Book id: 1, author id: 2 Same with categories and other 1:N relations Olaf On 2/9/09 2:01 PM, "PJ" <af.gour...@videotron.ca> wrote: > Peter Brawley 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. > I did review normalization - I had read it before; it is a little > clearer now, but.... > so, now I'm getting very very confused... > As I understand it, I have one table "books" it lists all the info for > the book other than the author(s) or the categories ; for these I need > an authors table and a category table... I'll continue with the authors > as categories will surely be the same thing. BTW, I cannot use ISBN as > PK since there are books without ISBN that are older than ISBN itself. > So, there is no author or category field in the books table, right? > Are you saying that the id PK of books, authors and books_authors are > all the same? > The "authors" table would have the fields auth_id, first_name, last_name. > The "books_authors" table would have its own fields - id PK, bid, aid > and listed_order (which would indicate iin which order to display the > authors (?)) > Where things go awry is in how to keep track of all this? Especially, > when I have to enter the information in the main table, which is books. > Every time I have a new listing I have to enter the main book info in > the books table, the authors in the authors table and the rest in > books_authors table... not to mention the categories - I don't suppose > there is a simple solution to this? > From the looks of things I have to create some kind of php input > function or group of functions to come up with a page with the fields > necessary to enter all the data and then store the data in mySql. And to > retrieve the information its a heap of functions to gather and populate > a page with the info from mySql... >> >> 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
