Re: RE : Re: database design ...

Raymond O'Donnell wrote:
> Apologies, my reply should have gone to the list.
>
> To answer your question, the sort of thing I'm thinking of is the
> case where, maybe, one copy of a book is missing a page or two (not
> unknown in a school library) - the first scenario can't record this,
> nor can it tell which unlucky borrower ended up with this copy.
>
> Or perhaps one copy of a book is signed by the author and so is
> reserved in the library: the system should not allow it to be lent,
> but unless you have a separate row for each copy, it can't
> distinguish between them.
>
> In the end, you need to decide whether you have a need to distinguish
> between various copies of the same title. If there is any chance at
> all that will need to, either now or in the future, then your life
> will be made much easier by splitting the books into two tables.
>
You could get by with a single table using a title and copy_no.

The title is your single table implementation, but each copy of each
title is a separate record. The barcode is specific to each copy.

Personally I'd normalise this to more tables & use keys, having
basically one flat table for all books is a structure I can see becoming
very cumbersome pretty quickly when you come to actually use it.

eg:

t_copy
copy_key #used on bar code, unique for every copy of every
book. (Either serial or oid?)
title_key #foreign key to table of titles
author_key #foreign key to table of authors
condition #available/damaged, also via a key to a table of
condition codes

etc. Titles lists the titles, may include a redundant (but useful)
foreign key on author_key

Use views to join copy_key, title, author, loan status, etc.

A casual user could still see a view as the basic flat single table
structure, but the underlying data is better managed.

Brent Wood