List:General Discussion« Previous MessageNext Message »
From:mos Date:December 29 2008 3:56pm
Subject:Re: how to design book db
View as plain text  
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.

I think the best line of attack is to work from an existing model. Why 
re-invent the wheel?

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 

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