List:General Discussion« Previous MessageNext Message »
From:mos Date:January 6 2009 3:14am
Subject:Re: how to design book db
View as plain text  
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 --- pj@stripped
>    http://www.ptahhotep.com
>    http://www.chiccantine.com

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