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.gour...@videotron.ca> 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 --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
