Book Loan Database
in [Tables]
|
From: Kris D on 4 Jul 2008 01:47 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
From: scubadiver on 4 Jul 2008 03:58 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) "Kris D" wrote: > 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 >
From: Michael Gramelspacher on 4 Jul 2008 07:07 On Thu, 3 Jul 2008 22:47:00 -0700, Kris D <KrisD(a)discussions.microsoft.com> wrote: >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));
From: Chris O'C via AccessMonster.com on 4 Jul 2008 14:41 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 scubadiver wrote: >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 -- Message posted via http://www.accessmonster.com
From: Fred Boer on 4 Jul 2008 15:13
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). -- Fred Boer - Amateur Access Enthusiast Interests: Library software / Z39.50 / Web Services Freeware Small Library Application available here: http://www3.sympatico.ca/lornarourke/ "Kris D" <KrisD(a)discussions.microsoft.com> wrote in message news:936FC132-A9B0-477B-98F6-FC73850170A5(a)microsoft.com... >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 > |