Thread: RE : Re: database design ...
X-No-Archive: true
Just had a thought. If you see ..
.. I'd planned to have one table 'stock' and a column in that table for 'format', as we have books, CDs, DVDs, etc...
What about if I had a separate table for books, another for DVDs, etc? The same model that you suggested, i.e. one table for stock in a sort of 'abstract' sense, and each of the other tables denoting the 'physical' objects (i.e. referring to the actual media _objects_) with a foreign key pointing back to the first table? That way, every single item in the building has its own unique ID, but the different copies of the various media are divided across tables?
D.
Raymond O'Donnell <rod@iol.ie> a écrit :
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
----------------------------------------------------------------------
--
Des Coughlan
Des Coughlan
"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.
Desmond Coughlan wrote: > X-No-Archive: true > > Just had a thought. If you see .. > > http://www.chez.com/desmondcoughlan/unix/cdi_gt.sql With the ISBN number you may want to look at contrib/isn - this adds ISBN types. If not then varchar(12) won't hold the new ISBN-13 format that is in full effect next year. From the readme - This directory contains definitions for a few PostgreSQL data types, for the following international-standard namespaces: EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials). This module is inspired by Garrett A. Wollman's isbn_issn code. I wanted the database to fully validate numbers and also to use the upcoming ISBN-13 and the EAN13 standards, as well as to have it automatically doing hyphenations for ISBN numbers. This new module validates, and automatically adds the correct hyphenations to the numbers. Also, it supports the new ISBN-13 numbers to be used starting in January 2007. -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz
X-No-Archive: true
Excellent.. thank you for that!
D.
Shane Ambler <pgsql@007Marketing.com> a écrit :
Shane Ambler <pgsql@007Marketing.com> a écrit :
Desmond Coughlan wrote:
> X-No-Archive: true
>
> Just had a thought. If you see ..
>
> http://www.chez.com/desmondcoughlan/unix/cdi_gt.sql
With the ISBN number you may want to look at contrib/isn - this adds
ISBN types.
If not then varchar(12) won't hold the new ISBN-13 format that is in
full effect next year.
From the readme -
This directory contains definitions for a few PostgreSQL
data types, for the following international-standard namespaces:
EAN13, UPC, ISBN (books), ISMN (music), and ISSN (serials). This module
is inspired by Garrett A. Wollman's isbn_issn code.
I wanted the database to fully validate numbers and also to use the
upcoming ISBN-13 and the EAN13 standards, as well as to have it
automatically doing hyphenations for ISBN numbers.
This new module validates, and automatically adds the correct
hyphenations to the numbers. Also, it supports the new ISBN-13
numbers to be used starting in January 2007.
--
Shane Ambler
pgSQL@007Marketing.com
Get Sheeky @ http://Sheeky.Biz
--
Des Coughlan
Des Coughlan
"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.