Book Loan Database

[Kris D]

I am trying to create a database for a book program that I run. I am
currently using an excel spreadsheet to track all books in inventory and who
they are checked out to, but I know a database would be a lot easier so that
more than one person can make transactions at the same time.

The problem I have is I don't know what should be set as the primary key,
and how I should se the relationships. I made three tables; Students table
(fields: social, student ID, name, address, phone number), book table
(fields: ISBN number, title, author, category, purchase price, discontinue
date), and transactions table (fields: Book, Checked Out To, Checked Out
Date, Due Date, Checked In Date).

I have several copies on most of the books in inventory with the same ISBN
number, and wasn't sure how to distinguish each book as a different copy and
which field should be a primary key. I thought of usig the ISBN number as a
primary key and at the end of each ISBN number place a dash 01 for the first
copy, dash 02 and so on???

The second problem I am trying to do is relate all three tables together.
One student can check out as many books as they want, so I wasn't sure if
about the one-to many relationships, or one-to-one relationships.

Can someone give me some suggestions on how to create this database?

Thanks

 

[scubadiver]

Someone else may correct me on this but I can't see that having multiple
copies should be a problem. If you have 5 copies of a textbook and they are
all borrowed then there is no reason to distinguish them. Why would you think
so? (this isn't a trick question)


book table
book id (PK)

student table
student id (PK)

transaction table
book id (FK)
student id (FK)
transaction id (PK)

 

[Michael Gramelspacher]

I am trying to create a database for a book program that I run. I am
currently using an excel spreadsheet to track all books in inventory and who
they are checked out to, but I know a database would be a lot easier so that
more than one person can make transactions at the same time.

The problem I have is I don't know what should be set as the primary key,
and how I should se the relationships. I made three tables; Students table
(fields: social, student ID, name, address, phone number), book table
(fields: ISBN number, title, author, category, purchase price, discontinue
date), and transactions table (fields: Book, Checked Out To, Checked Out
Date, Due Date, Checked In Date).

I have several copies on most of the books in inventory with the same ISBN
number, and wasn't sure how to distinguish each book as a different copy and
which field should be a primary key. I thought of usig the ISBN number as a
primary key and at the end of each ISBN number place a dash 01 for the first
copy, dash 02 and so on???

The second problem I am trying to do is relate all three tables together.
One student can check out as many books as they want, so I wasn't sure if
about the one-to many relationships, or one-to-one relationships.

Can someone give me some suggestions on how to create this database?

Thanks

This is an excerpt of a message from from Joe Celko.

CREATE TABLE Titles
(isbn CHAR(10) NOT NULL PRIMARY KEY,
title VARCHAR (70) NOT NULL,
author VARCHAR (40) NOT NULL,
publish_date DATETIME NOT NULL,
synopsis VARCHAR(250) NOT NULL
DEFAULT 'None available');


CREATE TABLE Copies
(isbn CHAR(10) NOT NULL --assumption about business rules
REFERENCES Titles (isbn)
ON DELETE CASCADE
ON UPDATE CASCADE,
copy_nbr SMALLINT NOT NULL,
purchase_date DATETIME NOT NULL,
purchase_price DECIMAL(12,4) NOT NULL
DEFAULT (0.00), -- donation as default
on_loan CHAR(1) NOT NULL DEFAULT 'n'
CHECK (on_loan IN ('y','n'),
PRIMARY KEY (isbn, copy_nbr));


CREATE TABLE Loans
(isbn INTEGER NOT NULL
REFERENCES Titles (isbn)
ON DELETE CASCADE
ON UPDATE CASCADE,
copy_nbr SMALLINT NOT NULL,
borrower_ssn INTEGER NOT NULL
REFERENCES Staff (ssn),
checkout_date DATETIME NOT NULL,
due_date DATETIME NOT NULL,
return_date DATETIME NOT NULL,
CHECK (out_date <= due_date),
PRIMARY KEY (isbn, copy_nbr, ssn), --business rule
FOREIGN KEY (isbn, copy_nbr)
REFERENCES Copies (isbn, copy_nbr));

 

[Chris O'C via AccessMonster.com]

If Jerry, Paul and Steve each borrow a copy of "The Da Vinci Code" and two
copies are returned on time in the book return bin, who are we going to send
the overdue notice to?

If only we had a way to distinguish between the copies...

Chris
Microsoft MVP

Someone else may correct me on this but I can't see that having multiple
copies should be a problem. If you have 5 copies of a textbook and they are
all borrowed then there is no reason to distinguish them. Why would you think
so? (this isn't a trick question)

book table
book id (PK)

student table
student id (PK)

transaction table
book id (FK)
student id (FK)
transaction id (PK)

I am trying to create a database for a book program that I run. I am
currently using an excel spreadsheet to track all books in inventory and who

[quoted text clipped - 21 lines]

Thanks

 

[Fred Boer]

Dear Kris D:

I've dealt with this issue in my library software by avoiding the use of
ISBN as a primary key, since I decided ISBN was not a suitable primary key.
Every item in the library table is given a unique identifying "BookID"
number. (Autonumber would work, although I generate a number in code.)

All books receive a "Copy Number", as well, which is incremented for every
new copy of the book received. Copy numbers are not reused - i.e. if a book
is lost that "CopyNumber" is not applied to another book.

I wouldn't mess with the ISBN to try to make it into a primary key by adding
numbers to it or manipulating it as you may make searching for books by ISBN
difficult.

One idea is to use a junction table for circulation: "tblCirculation" would
consist of 4 (or more) fields with each record containing a field for
BookID, PatronID, DateBorrowed and DateReturned. A record is created in this
field when a book is borrowed, with BookID, PatronID, and the current Date
entered. When a book is returned you locate this record and enter the date
returned. tblPatrons would be 1 to Many with tlbCirculation as would
tblLibrary. Queries can derive overdues, etc.

You might like to look at my library applicaiton for ideas in developing
your own. It is free, and is unprotected (i.e. you can look and modify all
aspects of the application - just hold down the shift key to get to the
database window).

 

[scubadiver]

http://www.databaseanswers.org/data_models/index.htm

go to the number 82 for some ideas.