On Tue, January 6, 2009 13:30, PJ wrote: > My comments, questions & explanations inserted below > > mos wrote: >> At 09:55 AM 1/6/2009, you wrote: >> >>> >-----Original Message----- >>> >From: c...@l-i-e.com [mailto:c...@l-i-e.com] >>> >Sent: Tuesday, January 06, 2009 9:47 AM >>> >To: mysql@lists.mysql.com >>> >Subject: RE: how to design book db >>> > >>> > >>> >Just theories here: >>> > >>> > >>> > >>> >The same book re-issued by another publisher might have a different >>> >ISBN. >>> > >>> > >>> > >>> >A book with an insert (e.g., CDROM) may have a different ISBN, but be >>> >the "same" for some purposes. >>> > >>> > >>> > >>> >And mistakes can be made... >>> > >>> > >>> > >>> >Ultimately, I suspect that the uniqueness of ISBN to what normal folks >>> >call the "same book" is not as clear as one would hope. >>> > >>> > >>> [JS] I'm really glad I was able to eavesdrop on this conversation. I >>> had no >>> idea the ISBN issue was so murky. >>> >>> For better or worse, most of "my" publishers don't use ISBNs; many of >>> them >>> don't even assign product numbers. >>> >>> I guess the only way around it is to assign your own unique key. >> > Not only do I have 2 ISBNs for a few books, but there is also the > problem of books in foreign languages (French, Italian, German, > Spanish) - my boss (my daughter who owns http:// www.ptahhotep.com ) > tells me that foreign editions do have different ISBN numbers .... but > not to worry, they are diffeerent but they are unique and would not > conflict with the US numbers. >> BTW, it might be worth while for PJ to look at how Amazon stores its >> data. I don't think you can find an easier to use database for >> searching on books. It looks like they store 2 ISBN numbers per book. >> It also appears they use fulltext indexing on a lot of fields so the >> user can search on anything. > I'll look at the Amazon stuff. Thanks for the suggestion. > > But this is where I get a little bit muddled: > I have a problem (probably just understanding how things work) with > categories. Since there are many categories (somewhere like 40+), how do > I handle that? Would it be best to set up foreign keys for a categories > table? > But then there is the problem of fulltext indexing... it only works with > MyISAM but foregn keys only work with InnoDB? > So do I use MyISAM, a categories field, and just use 1 table for books > with fulltext indexing on description, title, author, and categories > tables? > > And what about fulltext indexing? Do I really need that? I thought it > would be sufficient to search the fields for words or phrases (in the > case of categories which often will be like "second intermetiate period"). > > In effect, what I expect to be doing is to use php to format the pages > dynamically using the search functions of MySQL - so the categories > would be on a static page with javascript dropdown fields for the > different categories which would poinnt to a file that would do the > required search and php would then set up the page to display the > retrieved info. You could have a category table, a table to relate categories to categories and another table to relate books to categories. That's the general solution since books could be in multiple categories. I did a system like that for a bookstore but they didn't want to put the books into categories and just wanted to search. If you are using a procedural language you can use that instead of foreign keys to enforce consistency then you can us MyISAM tables. > > >> >> >> Also check out http://www.abebooks.com/ >> >> re: ISBN numbers. If this application is for a used bookstore then >> you're going to have to allow books without ISBN's because books from >> 40 years ago of course don't have ISBN's. > No, this is not for a used bookstore... only for the ptahhotep site > which is only a bibliography of books on Ancient Egypt. :-) - so you're > right, there are old books in there. > PJ > >> >> >> Also see http://en.wikipedia.org/wiki/Isbn. >> >> Mike >>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
