Thread: 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@iol.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@iol.ie > ---------------------------------------------------------------------- > > > > > > --------------------------------- > Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinionset des expériences des internautes sur Yahoo! Questions/Réponses. ---------------------------------------------------------------------- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland rod@iol.ie ----------------------------------------------------------------------
X-No-Archive: true
OK, I think I understand. So the *.sql file that I provided doesn't need to be changed per se, as in the 'stock' table is OK (maybe change it 'stock_general')? I'd just add another table, with a foreign key 'pointing' back to 'stocks_general'... and a sequence, of course, so that every _physical_ copy of the book has its own id ?
D.
Raymond O'Donnell <rod@iol.ie> a écrit :
Raymond O'Donnell <rod@iol.ie> a écrit :
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 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@iol.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@iol.ie
----------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
Des Coughlan
Des Coughlan
"Un client de plus, c'est un relou de plus..."
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
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