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