List:General Discussion« Previous MessageNext Message »
From:Paul Wilson Date:January 6 2009 1:59am
Subject:RE: how to design book db
View as plain text  
Recent books that I've looked at have 2 ISBN's - one the older 10 digit, and also the
newer 13 digit version. Both printed on the same book (both on the back cover at the
bottom and inside).

Of course, a hard cover will have a different ISBN again.

Hooker
--
" If ignorance is bliss, politicians should be orgasmic!"

-----Original Message-----
From: bruce [mailto:bedouglas@stripped]
Sent: Tuesday, January 06, 2009 8:52 AM
To: 'PJ'; 'mos'
Cc: mysql@stripped
Subject: RE: how to design book db

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.gourmet@stripped]
Sent: Monday, January 05, 2009 3:06 PM
To: mos
Cc: mysql@stripped
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 --- pj@stripped
   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=1


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
how to design book dbPJ29 Dec
  • Re: how to design book dbmos29 Dec
    • Re: how to design book dbPJ6 Jan
      • RE: how to design book dbbruce6 Jan
        • RE: how to design book dbPaul Wilson6 Jan
          • RE: how to design book dbbruce6 Jan
      • Re: how to design book dbmos6 Jan
RE: how to design book dbceo6 Jan
  • RE: how to design book dbJerry Schwartz6 Jan
    • RE: how to design book dbWm Mussatto6 Jan
    • RE: how to design book dbmos6 Jan
      • Re: how to design book dbPJ6 Jan
        • Re: how to design book dbWm Mussatto6 Jan
          • RE: how to design book dbbruce6 Jan