Re: DB Layout

>>>>> "DR" == Dave Rolsky <[email protected]> writes:

  >> book_authors
  >> ------------
  >> 
  >> book_id
  >> author_id

  DR> Yep, brain fart on my part.  But that table would probably need an
  DR> additional column, something like this:

i thought something stunk around here. :)

  DR>   BookPerson
  DR>   ----------
  DR>   book_id
  DR>   person_id
  DR>   role_id

  DR>   Role
  DR>   ----------
  DR>   role_id
  DR>   role -- editor, author, ...

do we need to id roles?

  DR> I think distinguishing between reviews and comments needlessly complicated
  DR> both the schema and the user interface.  How are they distinguished?  Who
  DR> decides?

well, if they are text in our db then they could be the same.

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

  DR> Gack.  Null hell.  Reviews which are just links can go in the BookLink
  DR> table.

i don't agree as we want to list reviews separately from other book
links. maybe a link_type that says what kind of link: cover, home page,
review, etc. then we can grab all the review links and our text review
entries and make one listing of reviews.

we haven't covered ratings either. a new table with user_id, book_id and
rating? we can keep the running average in the book table itself to make
it easy to sort by ratings. also we would want a user to be able to
filter out foe ratings as well as reviews. who would want a dummy's votes to
matter?

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