Library system in access [help and advice plz]

S

shanmukha.kovvuri

hey im currently creating a small library system for my college
project, and i need some help and advice.

My tables are;

* PK = Primary Key

tblBook
(PK)ISBN
Title
AuthorID
PublisherID
NumberOfCopies
CategoryName
PublicationYear
Picture

tblBookCopy
(PK)CopyID
ISBN
DatePurchased
ReplacementCost
Lending

tblBookLoan
(PK)LoanID
CopyID
MemberID
DateDueBack

tblMember
(PK)MemberID
Name
Surname
Sex
Address1
Address2
PostCode
TelNumber

i also have other tables, Author, Publisher, Category.
anyway, since ISBN is unique to each title of book, if i have multiple
copies of that book, each will have to be identitfied, im using
CopyID. For example NumberOfCopies of a book in tblBook is 5, how can
tblBookCopy be made to add 5 unique entries to of that Book, if you
understand wat i mean. also im new at this, but any help will be much
appreciated.
 
J

John W. Vinson

i also have other tables, Author, Publisher, Category.
anyway, since ISBN is unique to each title of book, if i have multiple
copies of that book, each will have to be identitfied, im using
CopyID. For example NumberOfCopies of a book in tblBook is 5, how can
tblBookCopy be made to add 5 unique entries to of that Book, if you
understand wat i mean. also im new at this, but any help will be much
appreciated.

Your design looks excellent; but for this one problem, I'd suggest a
different solution. Don't store NumberOfCopies at all!

Instead, use a Form based on tblBook with a subform based on
tblCopies. When you're entering books, simply add as many records
(each with its own purchasedate and other copy-specific fields) to the
Subform. You can calculate the number of copies using an unbound
textbox on the footer or header of the subform, with a controlsource

=Count(*)

or you can calculate it wherever it's needed using DCount().

John W. Vinson [MVP]
 
F

Fred Boer

Dear John:

Well, this interests me! In my library app, I include a copy number in the
main table (see structure below). In the structure suggested by the OP, you
would enter only one record in the main table for the book itself, and then
instances of that book would be tracked using the "copies" table. So, the
main table wouldn't refer to a specific physical copy or "instance" of a
book, but would have information about the book in a more general sense. The
table "tblCopies" would have records that would refer to a specific physical
book. Hmm... I guess in my structure I'd end up with a lot of duplicated
information, eh? (i.e. If I have 5 copies of a book, then I'd duplicate the
"Title", etc., 5 times).

1. So is the OP's structure better?
2. So, if a copy of a book was lost, then that information would have to be
retained in a field in tblCopies, correct? So to get an accurate count,
you'd need to flag the "lost" copy and not include it... Thus, you can't
rely on the "copy number" to give you an accurate count of number of
copies - as you say, you need to do a Dcount() of ISBNs based on a filtered
set of tblCopies.
3. And if all copies of the book were removed from the library, you'd need a
flag on the main table, as well, so that when you removed the last copy,
you'd automatically flag the related record in the main table as "deleted".

I need to think about this..! :)

My strucure:

Note: I don't use ISBN as a primary key because in the library for which I
first began developing this application, there could be numerous formats
(braille, large print) of the same book with the same ISBN, so ISBN wasn't a
good candidate for PK....


tblLibrary

BookID (PK)
Title
Subtitle
DeweyNumber
ISBN
CopyNumber
NumberOfVolumes
VolumeNumber
NumberOfElements
PubYear
Cost
etc...
 
F

Fred Boer

Just to add a bit more info about my structure...

tblLibrary

BookID (PK)
Title
Subtitle
DeweyNumber
ISBN
CopyNumber
NumberOfVolumes
VolumeNumber
NumberOfElements
PubYear
Cost
BindingID
PublisherID
PubPlaceID
StatusID
ProcessDate
ModifiedDate
DeletedRecordFlag
etc...


Junction tables for multiple authors/subject headings:

tblBookAuthor

BookID
AuthorID

tblAuthor

AuthorID
AuthorFirstName
AuthorLast Name
etc...

tblBookSubject

BookID
SubjectID

tblSubject

SubjectID
SubjectHeading

Linked tables for various information...

tblBinding

BindingID
BindingCode

tblPublisher

PublisherID
PublisherName

and so on with other linked tables...

Fred
 
J

John Nurick

tblBookLoan
(PK)LoanID
CopyID
MemberID
DateDueBack
tblMember
(PK)MemberID
Name
Surname
Sex
Address1
Address2
PostCode
TelNumber

I've never run a library, but I wonder whether this is sufficient.
Presumably the idea is that when a book is returned the corresponding
BookLoan record is deleted; the problem I see with that is there is no
way to identify "bad" readers - e.g. those who mutilate books (something
that's more often discovered by a later borrower than by library staff).

On the other hand maybe this is an intentional weakness to comply with
an overprotective privacy policy.
 
J

John Nurick

tblBookLoan
(PK)LoanID
CopyID
MemberID
DateDueBack

Also: LoanID is an artificial key that's not referenced anywhere else in
the structure you've described - so it shouldn't exist. The primary key
should be CopyID, DateDueBack.

Thinking a bit further, maybe there should be three date fields:

CopyID*+
DateIssued*
MemberID+
DateDueBack
DateReturned

As indicated by *, the PK would be CopyID, DateIssued. Unless it is
impossible for loans to be renewed or extended, DateDueBack cannot
always be calculated from DateIssued. And DateReturned means it is not
necessary to delete the record when a book is returned, making it
possible not only to identify bad borrowers but also to discover how
often various titles are borrowed and how long they are kept out.

Because the MemberID field is not part of the primary key, privacy
concerns could be met by deleting the MemberID value after a certain
time or number of subsequent loans; that way, you would still have the
historical information on book usage.
 
J

John Nurick

Hi Fred,

Isn't there a 1:M relationship between DeweyNumber and ISBN (at least
for any given library)?

Or is it a 1:M relationship between DeweyNumber and ISBN for any given
cataloguer in any given month<gd&r>.

Also (a question for both you and the OP): don't you need a Location
field or some such for each copy of each book? (E.g. "open shelves",
"stack", "closed reserve", "bindery", "unknown".)
 
F

Fred Boer

Hi John:
Or is it a 1:M relationship between DeweyNumber and ISBN for any given
cataloguer in any given month<gd&r>.

Or before and after lunch? :)

Yes, I think you are correct. So, I guess I could break out the Dewey field
into its own table, and do a table lookup for that field... Well, maybe in
the next complete rebuild! As to the location - yes, you *do* want a
location field, and I actually *have* one - I just forgot to include it (I
was typing from memory). I also have fields to track book format (Print,
Braille, Talking Book, etc.), and a memo field for notes, and a junction
table for a Series title...

The way the OP has it set up intrigues me, however, as it kinda turns what
I've done on its ear: my "main" table (which contains title information)
would become a lookup table for the "copies" table, (at least in the way my
mind imagines it), and the "copies" table is really what I would think of as
the "main" table - i.e. the one whose records actually contain information
on the individual physical books themselves. I don't think what I've done is
*wrong*, and it works fine, but perhaps it isn't as "right" as it could be!

Cheers!
Fred
 
J

John Nurick

The way the OP has it set up intrigues me, however, as it kinda turns what
I've done on its ear: my "main" table (which contains title information)
would become a lookup table for the "copies" table, (at least in the way my
mind imagines it), and the "copies" table is really what I would think of as
the "main" table - i.e. the one whose records actually contain information
on the individual physical books themselves. I don't think what I've done is
*wrong*, and it works fine, but perhaps it isn't as "right" as it could be!

That seems the natural way to do it to me, given the 1:M relationship
between copies and books (also, of course, between volumes and books -
or is that between physical and logical books?).

BTW, please can you reassure me about ISBNs. I always thought that each
edition of a book got its own ISBN (hardcover, paperback, large print
etc.) but you mentioned earlier that different formats in your library
shared the same ISBN. Is that because the braille and talking book
formats don't count as published books and therefore don't get their own
ISBNs?
 
F

Fred Boer

Hi John:
BTW, please can you reassure me about ISBNs. I always thought that each
edition of a book got its own ISBN (hardcover, paperback, large print
etc.) but you mentioned earlier that different formats in your library
shared the same ISBN. Is that because the braille and talking book
formats don't count as published books and therefore don't get their own
ISBNs?

Exactly. I still have my doubts about ISBN as a primary key - I can think of
a number of things in my library that wouldn't have an ISBN - multimedia
kits, older books, small press or self-published books, and so on. I wonder
what key value libraries would manage these non-ISBN materials, anyone know?

Cheers!
Fred
 
F

Fred Boer

Hey, John:

I was thinking about all this while I made my kid's lunches (anything to
take my mind off that chore!)...

So, would you actually need to take this further? I mean, wouldn't a ISBN be
in a 1:M relationship with Title? There could be more than "book" with the
same title, right? Or is there a flaw in my thinking? Or is this taking it
to the absurd? When I look at the list of fields I have in my original
tblLibrary (see above in thread...) I'm wondering how many more of those
fields should actually be broken out in a 1:M relationship.. Hmm..

Cheers!
Fred
 
F

Fred Boer

Actually, I think in the future I better not think about Access while making
my kid's lunches. I think I sent one of them to school with a peanut butter
and mortadella sandwich! ;)

Fred

Fred Boer said:
Hey, John:

I was thinking about all this while I made my kid's lunches (anything to
take my mind off that chore!)...

So, would you actually need to take this further? I mean, wouldn't a ISBN
be in a 1:M relationship with Title? There could be more than "book" with
the same title, right? Or is there a flaw in my thinking? Or is this
taking it to the absurd? When I look at the list of fields I have in my
original tblLibrary (see above in thread...) I'm wondering how many more
of those fields should actually be broken out in a 1:M relationship..
Hmm..

Cheers!
Fred
 
F

Fred Boer

Dear John:

In my app, I have a Transaction table (see below). It never occurred to me
to delete the transaction information for privacy concerns... I don't have a
way to track "bad" patrons, although, of course I can generate overdues for
a given patron. When a book is returned, the process includes a way to flag
a change of status in the book (eg. "Out for Repair", "Deleted"). I suppose
it wouldn't be too hard to expand on this so that, for example, if a book is
marked as "Out for Repair", or "Returned Damaged" on "check in" a field
could be updated in the patron's record to indicate this...

Cheers!
Fred

tblTransaction

TransactionID
BookID
PatronID
TransactionTypeID (different transaction types may have differing loan
periods, etc.)
TimeOut
TimeIn
 
G

Guest

Fred,

I'm trying to create a similar application for demo stock. You say you can
change the status of an item when it comes back in, how do you do that?

At the moment, I have an Assets table of which Status ID is an attribute of
each Asset record. I need to have it change to reflect an asset's staus
whenever it is booked out to a prospective customer, or checked back in by
our QA Dept, or sent out for repair etc.
 
F

Fred Boer

Hi VT:

As items are "checked in", they are listed on a subform. On the subform is a
combobox, for which you can select one of several indicators, (On Shelf
(default); InterLibrary Loan; Repair; Lost, Discarded). So it is checked in,
then marked as "Repair", etc.

If you are really curious, and want to see how it works, you can download my
application (it is free) and have a look: www3.sympatico.ca/lornarourke

Cheers!
Fred

P.S. Actually the screen shot of the "Check In" module might even be enough
to give you the idea, even without downloading/installing the app...
 
J

John Nurick

Hi Fred,

Sometimes I'm glad I'm not a librarian!

I *feel* that a Title is best treated as an attribute of a book, not as
an entity in its own right - precisely because there can be more than
one book with the same title (but different authors and subjects). You
could even have two books with the same title by different authors who
happened to have the same name.

But if we're taking normalisation seriously <g> shouldn't we have a sort
of metabook entity? The "Dickens, Charles: Great Expectations" metabook
would be in a 1:M relationship with all the versions, editions, variant
texts, transcriptions (braille, talking book...), translations,
adaptations (stage, screen, audio, BD/anime...)....






Hey, John:

I was thinking about all this while I made my kid's lunches (anything to
take my mind off that chore!)...

So, would you actually need to take this further? I mean, wouldn't a ISBN be
in a 1:M relationship with Title? There could be more than "book" with the
same title, right? Or is there a flaw in my thinking? Or is this taking it
to the absurd? When I look at the list of fields I have in my original
tblLibrary (see above in thread...) I'm wondering how many more of those
fields should actually be broken out in a 1:M relationship.. Hmm..

Cheers!
Fred
 
F

Fred Boer

Dear John:
Sometimes I'm glad I'm not a librarian!

<Chuckle> Sometimes I pretend I'm not one! :)

An anime version of Dickens! ROFL! Big-eyed women in sailor suits on the
streets of Victorian England... <G>

Thank you for following up with me on this thread. I think maybe I've
reached the limits of what I can cope with in terms of normalizing to the
Nth degree... If and when I ever again rebuild the library app from scratch,
I might consider creating the tblBookInfo/tblCopyInfo structure, but I think
that's as far as I'll go! What with "metabook entities" and all, I'm
starting to feel like I'm in the middle of a Star Trek episode, and
McCoy/Spock is looking at a tricorder and is telling me.. "It's life, Jim,
but not as we know it..." ;)

Many thanks!
Fred
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top