RE : Re: database design ...

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.

--Ray.

On 15 Nov 2006 at 0:47, Desmond Coughlan wrote:

> I have to confess the second model has me confused. Why can't the
> first one, i.e a single integer column for the number of copies, allow
> as much information to be stored? After all, each copy will be
> identical (some more worn out than others certainly.. is that what you
> meant?), but other than that, one table should be perfect .. no ? D.
>
> Raymond O'Donnell <rod(at)iol(dot)ie> a écrit :
> On 14 Nov 2006 at 19:34, Desmond Coughlan wrote:
>
> > I reckon we're not going to split stock into two tables, but your
> > point raises an important question. If I look over my shoulder, say we
> > take Spanish books. There are six or seven copies of each. Does each
> > one have an unique stock_id?
>
> As I see you, you could do it two ways.
>
> 1. Keep all stock records in one table, with each row representing a
> different title, and an integer column recording the number of copies
> of that title present in the library. Quick and easy, and if you
> don't need to record information about individual copies of one
> title, will do the job.
>
> 2. Split stock records into two tables. One has a single row for each
> title - name, author, ISBN, publisher, etc. The other table has a
> single row for each physical copy in your library, and has a foreign
> key back to the first table. This will allow you to record much more
> detail on each copy: hardback/paperback, condition, etc etc. It would
> also allow you to record which copy is on loan to which borrower.
>
> I personally would go with the second option: it requires a little
> more work initially, but allows you more flexibilty in the long run.
>
> --Ray.
>
>
> ----------------------------------------------------------------------
>
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod(at)iol(dot)ie
> ----------------------------------------------------------------------
>
>
>
>
>
> ---------------------------------
> Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses.

----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod(at)iol(dot)ie
----------------------------------------------------------------------