Re: database design ... - Mailing list pgsql-general

Hi,

Have you added the ability to store reservations, if a book is out.  Maybe
having a table for this,

requests/reservations
id pk
user_id fk
item_id fk
date_requested (so that the first person on the list for this book is
notified)

Also, I'll assume there is more than one book per title, thus having two
tables for the this relationship

Sock table as you have

than an
item table
id pk (say the barcode)
stock_id fk

This reduces redundancy for stock and make updates/alters easy.

pk - primary key
fk - foreign key

Cheers
Noel

On Wednesday 15 November 2006 09:52, Desmond Coughlan wrote:

> X-No-Archive: true
>
>   Thanks.  The main uses will be ..
>
>   1. available on www to query catalogue (open to everyone)
>   2 avail. from the web for teachers and students to check their library
> account (necessitates an account) 3. available in the library itself both
> on a web interface (to allow the librarian to check records, add/remove
> stock etc and to allow people physically present in the library to do 1 and
> 2 above), and on the barcode reader, for loans and returns.
>
>   I've modified a .sql file already found somewhere in an install's source
> doc... this is it..
>
>   http://www.chez.com/desmondcoughlan/unix/cdi_gt.sql
>
>   It's not much for the moment, but any tips?
>
> D.
>
>
>
>
>
>
> Ben <bench@silentmedia.com> a écrit :
>   It depends how you plan to use it?
>
> Maybe a helpful excercise for you to go through is to come up with some
> use cases and see if you are storing all the data you'll need in a way
> that makes it easy for you to use.
>
> On Tue, 14 Nov 2006, Desmond Coughlan wrote:
> > X-No-Archive: true
> >
> > Hi,
> > Thanks for all the help: we have our postgreSQL server on a 'backend'
> > machine, and the client on a webserver.
> >
> > The application I want to develop is a school library, and as this is new
> > to me, I come looking for ideas. Here's what I've done: on the backend,
> > two users (in addition to 'pgsql'): dba and 'cdi' (the name of the
> > library, as in the _premises_ where the library is located). I create a
> > database 'library', owned by dba, but with cdi having update privileges
> > (but not 'drop table' etc).
> >
> > 'library' has four tables...
> >
> > 1. users (with user_ids, surname, first_name, dob, address etc...)
> > 2. stock (stock_id, ISBN, title...)
> > 3. loans (loan_id, stock_id [foreign key to stock_id], date_due)...
> >
> > Is there anything else that such a db would need ?
> >
> > Thanks.
> >
> > D.
> >
> >
> > ---------------------------------
> > 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.
>
> ---------------------------------
>  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.

--
PhD Student
Department of Biochemistry and Molecular Biology
Monash University
Clayton, Victoria
Australia

Ph: +61 3 9905 1418
e-mail: noel.faux@med.monash.edu.au
website: http://vbc.med.monash.edu.au/~fauxn