At 05:05 PM 1/5/2009, you wrote:
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-creation-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"
I'm not sure what they mean by PF. Probably to indicate both fields as part
of the primary index.
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.
Primary keys should always be auto increment integers. You are using
TinyInt for Id's and that will only represent numbers from 0 to 255. You
need to change them to Integer or LongInt unisigned.
3. And what about books that were written by several authors?
To simplify multiple authors and ISBN's, why not add them to the book's
description or title field and have a full text index on it.
4. What do I need to fix in the tables below?
Change the TinyInts to Integer or LongInt and make them unsigned AutoInc.
Also I don't know why you need books_by_category or books_by_author tables
because to get books for a category or author you only need to do a search
on books. Table joins would slow things down considerably and you're not
using anything from these tables anyways.
Mike
+-------------------+
| 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=arch...@jab.org
