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
