RE : Re: database design ...

X-No-Archive: true

Just had a thought. If you see ..

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

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

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