Re: database design ...

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
whatI'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)...

Not sure how complete your list is but I would add user_id [foreign key
to users] to loans - so you know who to chase when it isn't back - stop
them borrowing if they have overdues?

Personally I would have stock as two tables - one with book details
(which can include details for titles you don't have and maybe a list of
requests for them to decide new purchases) and the other with a stock_id
of each copy that you have and include a reason for removing stock
(damaged/never returned/missing/unwanted).
Or is that your missing fourth table?


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz