MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 9 2009 5:04pm
Subject:Re: db setup - correction
View as plain text  

>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.



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.gourmet@stripped> 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 - 
> Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM

db setupPJ9 Feb
  • Re: db setupOlaf Stein9 Feb
  • Re: db setup - correctionOlaf Stein9 Feb
    • Re: db setup - correctionPJ9 Feb
      • Re: db setup - correctionPeter Brawley9 Feb
        • Re: db setup - correctionOlaf Stein9 Feb
        • Re: db setup - correctionPJ9 Feb
          • Re: db setup - correctionOlaf Stein9 Feb
          • Re: db setup - correctionPeter Brawley9 Feb
            • Re: db setup - correctionPJ10 Feb
              • Re: db setup - correctionPeter Brawley10 Feb
                • Re: db setup - correctionPJ10 Feb
                  • Re: db setup - correctionPeter Brawley10 Feb
                  • Re: db setup - correctionddevaudreuil10 Feb
                    • Size limitation of user variable?Bryan Cantwell10 Feb
                      • Re: Size limitation of user variable?Baron Schwartz10 Feb
                        • Re: Size limitation of user variable?Johan De Meersman11 Feb
                          • Re: Size limitation of user variable?Baron Schwartz11 Feb
Re: db setup - correctionPJ10 Feb