hi wm. take this for what it's worth!
i think you're trying to solve an issue that's already been solved. why don't you do some quick/dirty research on companies that either issue/manage isbn numbers, or companies who sell solutions for online/brick-mortar bookstores. explain that you're thiking of setting up a system (ie, might buy their product/services/solutions) but that you want to know more about the underlying database/schema regarding your issues... you'll be surprised at how much you can get from this kind of approach!!! in your case, you aren't looking to design a system that's going to be that different from what's already out there.... make the calls. and if you don't get any information from this process, what have you lost. on the other hand, you might find a resource who gives you everything you're looking for, and more! also, at some point, you're going to want to have a sample book database to test your db schema/app on.... have fun!! -----Original Message----- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Tuesday, January 06, 2009 1:38 PM To: mysql@lists.mysql.com Subject: Re: how to design book db 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=bedoug...@earthlink.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
