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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org