List:General Discussion« Previous MessageNext Message »
From:Olaf Stein Date:February 9 2009 5:29pm
Subject:Re: db setup - correction
View as plain text  
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.brawley@stripped> 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.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 - 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.stein@stripped


³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

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