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

And worse, some publishers re-use ISBM#s. In general, any PK dependency 
on the outside world is to be avoided unless the dependency guarantees 
uniqueness as robustly as the internal auto_increment facility.

 >So, there is no author or category field in the books table, right?

Right.

 >Are you saying that the id PK of books, authors and books_authors are
 >all the same?

Yikes no. Each is entirely independent.

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

You write a standard master-detail form, which usually has a single form 
at top for the parent row, and a browsing multi-row form below for entry 
of multiple child rows.

PB


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

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