Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.wood@xxxxxxxxxx> 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@xxxxxxxx
"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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux