RE : Re: RE : Re: database design ...

X-No-Archive: true

More tables and fewer columns in the tables? I *like* it! It hadn't occurred to me do it that way.

Will this work ?

http://www.chez.com/desmondcoughlan/unix/bibliotheque.sql

I haven't tested it yet, which brings me to two questions...

a. will it work even if I define the table 'stock' with foreign keys to tables that haven't been created? Or do I have to create the tables before I reference them? Invert the order of the file, so to speak?

b. is there a way to hose the db whilst I'm testing it without doing dropdb? I mean from within psql, rather than DROP <name of table or sequence> ; ? That's a bit long..

Thanks.

D.

Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz> a écrit :
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

--
Des Coughlan
coughlandesmond(at)yahoo(dot)fr

"Un client de plus, c'est un relou de plus..."

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