Thread: 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

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@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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
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)...
>
>  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
etdes expériences des internautes sur Yahoo! Questions/Réponses. 

And, when coming up with the use cases you should be working with the
people who will actually be using the application.  Wrt barcode
scanners, they typically just translate the barcode into a number.  So,
you'll need a barcode printer to print barcodes for your ids to put on
the books.

Ben wrote:
> 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.
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


--
erik jones <erik@myemma.com>
software development
emma(r)

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.

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

Desmond Coughlan wrote:

> And our ultimate aim is for a barcode reader to be used by the librarian.
 > Any good sources to learn about that ?

I have only had a little exposure to barcode scanners - the one that a
client used just behaved as a keyboard, so there was no programming to
support it, you simple click in the field you want to enter into and the
scanner types in the barcode number for you.

--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

Shane Ambler <pgsql@007Marketing.com> writes:

> I have only had a little exposure to barcode scanners - the one that a client
> used just behaved as a keyboard, so there was no programming to support it,

Besides this model there are also models that plug into the serial port and
also USB ports.  For both, the USB and keyboard models you don't have to
worry.  For the serial one you'll have to write code to read the value and
pass it to your application.

I recommend that you specify the USB model when buying: notebooks doesn't come
with external keyboard ports or serial ports anymore so if for some reason you
need to test it with yours or take the reader to somewhere you might have
problems with it.

> you simple click in the field you want to enter into and the scanner types in
> the barcode number for you.

The better if the app puts the cursor on the correct field...


Also remember that barcode readers can be programmed to "press" TAB / ENTER
after reading.  If you're on a web application this might submit the form
instantaneously so take care with that.  Most of them come with a default to
"press" ENTER after reading the barcode.

--
Jorge Godoy      <jgodoy@gmail.com>