hey phil... are you sure that a book can have multiple ISBN numbers. I was under the impression that a book/version has a single ISBN number.
care to share where you have derived your understanding... i believe bowkers/new jersy is responsible for allocating ISBN blocks for US authors/publishers... thanks -----Original Message----- From: PJ [mailto:af.gour...@videotron.ca] Sent: Monday, January 05, 2009 3:06 PM To: mos Cc: mysql@lists.mysql.com Subject: Re: how to design book db mos wrote: > At 08:17 AM 12/29/2008, you wrote: >> I am rather fresh to MySQL and am trying to fix and update a website >> - modifying from just plain html to css, php and MySQL. I'm working >> on FreeBSD 7.0, MySQL 5.1,30, PHP5.28 & Apache 2.2.11. >> I need figure out how to set up (design) a database of books which >> gets rather complicated since I must implement searches of the >> database based on key words including categories, ISBN numbers, >> authors, dates, etc. etc. >> The problem is how to deal with duplication of the data - In other >> words, a book may have not only several authors, but also several >> ISBN numbers, fall under several categories, different dates (year of >> publication), several publishers & I probably haven't yet seen all of >> the variables. >> I certainly do not want to enter the same book many times with just >> one of each different variable. I suppose that one way to do it is to >> enter one row with a lot of columns to store all the the different >> variables; a search would probably be simpler this way if the search >> criteria are limited to 1 word. Or would it? I rather do think that >> the search should be limited to 1 word anyway. :-) >> If the search would be for a category, for instance, would it make >> sense to use a column for category with an input of keywords for the >> different categories?; rather than a column for each category or >> another table of categories? >> Multiple publication years could probably be different row entries >> since there would not be more than 2 or would be a different >> publisher, language, or country. >> I really with to K.I.S.S this undertaking and would appreciate any >> help or suggestions. >> If it helps, you can see the site as it is at present >> http://www.ptahhotep.com - but since it is rather messed up at the >> moment, it is best viewed with IE. Some of the links and jscripts >> don't work on FireFox. >> TIA, >> PJ > > You can of course simplify things by putting the alternate ISBN number > in the description of the book and put a full text index on it. Same > with alternate authors etc.. It would be a catch all for items that > you don't have columns for. How do I do that? > > I think the best line of attack is to work from an existing model. Why > re-invent the wheel? You're right... I appreciate the suggestion and the links... > > There are a few bookstore/library schemas here: > http://www.databaseanswers.org/data_models/ > > http://www.edumax.com/oracle-basics-06-normalization-and-sample-schema-creat ion-normalization-and-sample-schema-creation.html > > > Mike > Sorry for the long "holiday" delay in continuing... I checked the links below & I think they will help ... but there are some things that are not clear in my mind: 1. I understand what the abbreviations PK and FK (primary key and foreign key) are but what is PF? (primary field??? - this is in the link http://www.databaseanswers.org/data_models/ uner "Libraries and books" 2. How can I deal with a primary key for books? ISBN would be great, except for the fact that it was only implemented at a certain date and books published before that date do not have an ISBN number. 3. And what about books that were written by several authors? 4. What do I need to fix in the tables below? +-------------------+ | Tables_in_biblane | +-------------------+ | authors | | books | | books_by_author | | books_by_category | | categories | +-------------------+ mysql> DESCRIBE authors; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | author_id | tinyint(4) | NO | PRI | NULL | | | auth_first_name | varchar(15) | NO | | NULL | | | auth_last_name | varchar(32) | NO | | NULL | | +-----------------+-------------+------+-----+---------+-------+ mysql> DESCRIBE books; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | tinyint(11) | NO | PRI | NULL | auto_increment | | title | varchar(60) | NO | | NULL | | | auth_name | char(28) | NO | | NULL | | | auth_first | char(12) | NO | | NULL | | | yr | year(4) | YES | | NULL | | | lang | char(7) | YES | | NULL | | | descr | tinytext | NO | | NULL | | | comment | text | NO | | NULL | | | e-mail | varchar(50) | NO | | NULL | | | publisher | varchar(50) | NO | | NULL | | | pub_link | varchar(32) | NO | | NULL | | | publisher1 | varchar(50) | NO | | NULL | | | pub1_link | varchar(32) | NO | | NULL | | | bk_cover | varchar(32) | NO | | NULL | | | isbn1 | varchar(20) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ mysql> DESCRIBE books_by_author; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | ahuthor_id | tinyint(4) | NO | | NULL | | | ISBN | varchar(20) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ mysql> DESCRIBE categories; +---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | category_id | tinyint(4) | NO | PRI | NULL | auto_increment | | category_name | varchar(32) | NO | | NULL | | +---------------+-------------+------+-----+---------+----------------+ mysql> DESCRIBE books_by_category; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | category_id | tinyint(4) | NO | | NULL | | | ISBN | smallint(20) | NO | | NULL | | +-------------+--------------+------+-----+---------+-------+ -- 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=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
