Re: DB Layout

>>>>> "s" == sfritz  <[email protected]> writes:

  s>  From Dave
  >>> 
  s> Here's a basic outline:

  s>    Book
  s>    -------
  s>    book_id
  s>    ISBN
  s>    title
  s>    edition
  s>    copyright_year
  s>    page_count
  s>    link

link to what?

  s>    publisher_id
  s>    UNIQUE( ISBN )
  s>    UNIQUE( title, edition )



  s>    Category
  s>    --------
  s>    category_id
  s>    category
  s>    parent_category_id -- whee, self-joins!

  s>    BookCategory
  s>    --------
  s>    book_id
  s>    category_id

  s>    BookLink
  s>    --------
  s>    book_link_id
  s>    book_id
  s>    book_link_type_id
  s>    link

i assume type_id is like home_page, cover_page, index, sample_chapter?


  s>    BookLinkType
  s>    --------
  s>    book_link_type_id
  s>    type -- cover, external reviews, excerpt, etc

ahh, just what i asked above.

  s>    Author
  s>    --------
  s>    author_id
  s>    name

where is a link from author(s) to books? this is many to many. to
properly normalize (i think) we need a new table which is

	book_authors
        ------------

	book_id
	author_id


  s>    Publisher
  s>    --------
  s>    publisher_id
  s>    name
  s>    link

  s>    User  -- may not be necessary, see below
  s>    --------
  s>    user_id
  s>    username
  s>    password

  s>    UserBookRating
  s>    --------
  s>    user_id  - PK
  s>    book_id  - PK
  s>    rating -- 1-10

  s>    UserBookComment
  s>    --------
  s>    user_id  - PK
  s>    book_id  - PK
  s>    comment

reviews needs a similar table. reviews could be directly in our DB or
links to other reviews on the web.

  s> UserBookComment could also include reviews, or we could somehow
  s> distinguish between comments ("OO Perl rocks") vs. longer reviews.

i think that we should have separate tables for comments and
reviews. and as i mentioned above, reviews could be links.

	reviews
	-------
	book_id
	user_id		(optional)
	text		(optional, internal review)
	url		(external review)
	url_title	(something which labels the reviewer or source)

the reviews page/section would generate a list of links to both our DB and
external ones. links to ours would be cgi's to fetch the review text.

  s> - it'd be nice to be able to tag books you're interested in and come back
  s> and see them later.

that would be a personal library! it would have to be under the user's
preferences tables or whatever. maybe this:

	library
        -------
	user_id
	book_id

  s> - links to online sellers?  partner programs?  I hate Amazon but maybe we
  s> could link to multiple sellers.

oh, definitely multiple sellers. see my other reply on how we would do
that.

good start. i think we need to do a few passes on this and then we could
start a basic DB going and work on insert and search code.

uri

-- 
Uri Guttman  ------  [email protected]  -------- http://www.stemsystems.com
-- Stem is an Open Source Network Development Toolkit and Application Suite -
----- Stem and Perl Development, Systems Architecture, Design and Coding ----
Search or Offer Perl Jobs  ----------------------------  http://jobs.perl.org