Re: DB Layout

On Thu, 31 Jan 2002, Uri Guttman wrote:

>   s>    Book
>   s>    -------
>   s>    book_id
>   s>    ISBN
>   s>    title
>   s>    edition
>   s>    copyright_year
>   s>    page_count
>   s>    link
>
> link to what?

I was thinking that this'd be the "official" but there's no reason to not
just stick that in the BookLink table.

>   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

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

  BookPerson
  ----------
  book_id
  person_id
  role_id

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

>   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.

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

> 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)

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


-dave

/*==================
www.urth.org
we await the New Sun
==================*/