List:General Discussion« Previous MessageNext Message »
From:PJ Date:February 10 2009 3:35pm
Subject:Re: db setup - correction
View as plain text  
Peter Brawley wrote:
> 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.
>
But what about foreign keys? Don't I need that to find the relationships
between the books, the authors and the categories? After all, isn't this
a relational db? If so, I can't use the default engine (MyISAM) which
does not support FK. So, if I have to use foreign keys, I have to change
to INNODB, right?


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


-- 

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