|
Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com |
From: bruce (bedouglas
earthlink.net)
Date: Mon Jan 05 2009 - 20:15:14 CST
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
right...
forgot about that!
-----Original Message-----
From: Paul Wilson [mailto:hookerstaff.iinet.net.au]
Sent: Monday, January 05, 2009 5:59 PM
To: 'bruce'; 'PJ'; 'mos'
Cc: mysqllists.mysql.com
Subject: RE: how to design book db
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:bedouglasearthlink.net]
Sent: Tuesday, January 06, 2009 8:52 AM
To: 'PJ'; 'mos'
Cc: mysqllists.mysql.com
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.gourmetvideotron.ca]
Sent: Monday, January 05, 2009 3:06 PM
To: mos
Cc: mysqllists.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 --- pjptahhotep.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=bedouglasearthlink.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=hookerstaff.iinet.net.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=bedouglasearthlink.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]