List:General Discussion« Previous MessageNext Message »
From:PJ Date:February 9 2009 4:53pm
Subject:Re: db setup - correction
View as plain text  
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.
>
>   


-- 

Phil Jourdan --- pj@stripped
   http://www.ptahhotep.com
   http://www.chiccantine.com

Thread
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