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

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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz