Access/Database question

[Hikaru12]

I'm having a hard time coming up with the fields/tables I need for a database I'm trying to design for my fellow college classmates. We're working on this program called the Book Exchange that is going to allow students to trade books with each other. We collect books from graduating seniors and then use those books to trade with other students. The students can either give us another book that they have in exchange for one of ours so we can always ensure we have a pool of new books or they can borrow the book for a semester which depletes our available pool of books. Anyway I'm trying to build a relational database in Access and I've run into a dilemma. I have no way of being able to create a relationship based on a condition.

 

What I imagine is this:

 

A table for the books we have in inventory with the following fields:

Book Table:
Subject
Book Title
Author - First, Last Name
Edition
ISBN
Copies

 

A table for the students information:

First Name:

Last Name:

Student ID #:

Phone Number:

Email:

(Condition) Rented/Exchanged:

 

If the student has rented a book from us then I want that book to be removed from the inventory (the book table). However, if the student has decided to exchange a book with us then I'd like the inventory to update to reflect that change (a book going out and a book coming in). Now, what the problem is setting up a condition that will check what the student intends to do and then updating the inventory accordingly. I haven't figured out a way to do that via Access. I'm wondering if any of you guys have a better idea or if I should really be programming this or if MYSQL is the best way to go about this (please keep in mind the end users of this will not be programmers or tech savy).

 

If you need any clarification please ask! Thanks!

[MikeD]

I think you should rethink your relational model. You have represented the entities book and student but you are missing the relations between those entities, which is at least borrow (and exchange if you are interested in knowing who exchanged what and not only which books you have).

You should not keep the count of how many of a certain book you have. The way databases are supposed to be used is you keep track of who borrowed what and calculate how many you have by making a query that subtracts the number of records for that book isbn on the borrowed table to the number of books with that isbn in the books table (you should have another id to distinguish different copies of the same book).

[Hikaru12]

How could I make it so that I know what student has what books AND what they gave us to obtain that book (if they decided to do an exchange instead of borrow the book)?

[WanderingFool]

You could just create another 2 tables.

 

Borrowed: (StudentId, ISBN)

Exchanged: (Studentid, ISBN_Deposited, ISBN_Withdrawn)

 

I think you should rethink your relational model. You have represented the entities book and student but you are missing the relations between those entities, which is at least borrow (and exchange if you are interested in knowing who exchanged what and not only which books you have).
You should not keep the count of how many of a certain book you have. The way databases are supposed to be used is you keep track of who borrowed what and calculate how many you have by making a query that subtracts the number of records for that book isbn on the borrowed table to the number of books with that isbn in the books table (you should have another id to distinguish different copies of the same book).

It can be alright to create a count, given all the entries will be the "same" otherwise.  It can speed up the lookups in the tables.

 

*edit....although it does mean you do sacrifice certain elements doing it the count way

[MikeD]

Ok. I'd do something along the lines of

Book:

ISBN (PK, unique, not null)

Subject
Book Title
Author - First, Last Name
Edition

 

Book copy: (many-to-one)

ID (PK, unique, not null)

ISBN (FK_Book)

 

Student:

Student ID # (PK, unique, not null)

First Name

Last Name

Phone Number

Email

 

Borrowed (one-to-many)

Student ID (FK_Student)

Book ID (PK, FK_Book copy) (only 80% sure about the PK and relation here!)

 

Exchanged

Student ID (FK_Student)

Book taken (PK, FK_Book copy) (I'd have to make a drawing to be sure about the relation and PK here)

New Book ID (FK_Book copy) (you must keep the removed book in the book copy table)

 

And then it's all about the order of the operations and only messing with the exchanged and borrowed tables (if you use triggers).

 

More detailed:

The student table is pretty simple. You add students!

The borrow table is also simple.

Now you have two options: you either build a set of queries to be applied in order to perform one operation (when someone exchanges a book you have one query to add the new book and another to populate the exchange table) or you use triggers and procedures so that when someone tries to exchange a book by adding an entry it will automatically add an entry to the book table and then carry on the add on the exchanged table.

The book and book copy tables are also simple, in the sense that after populating them (not sure how you intend to do this, if you have a pool of books to begin with or you will have students that donate books and you want to keep track of this, in which case you need more stuff) you should not have to mess with it directly if you use triggers (not sure if access has this, but it should). I separated book and book copy to keep track of different copies of the same book without replicating information (which is the point of databases!)

If you want to do it by hand (perform the queries by hand for each operation) you are done. If not, the following applies

The magic happens at the exchange table. You can define a procedure to be run every time someone tries to add something to the exchange table. In this case that procedure would be to add a book on the book table if it does not exist (based on the isbn of the new book) with the information supplied and then create a new entry in the book copy table. After the trigger has been processed the create operation would be executed normally.

 

Now, I realize this may be a bit overkill if you are looking for something more simple, but this would get you more marks if you were being evaluated. At least it would get me a better score!!

Also, if someone spots an error or disagrees please say so. It has been more than a year since my Databases class and now I am taking a Data Mining/Warehousing class which is a bit different and may be confusing me about what can be done!

[WanderingFool]

I guess it would depends on which way Hikaru decides to implement the system, but in a book borrowing situation, I would be inclined to still use a count rather than you "book copy" table.

 

If each book is treated as an individual object in the database, then it would imply that each item should be tagged with their unique ID.  While this on the software side might be good, the physical nature of putting id's to each book could be unwieldy....if Hikaru wants to setup a system where each book is tracked though (which if they have exchanges I guess he might), then I agree with your setup.

 

If books are not tagged, but rather just use their current barcodes to ID themselves, then there is no purpose to the book copy table as it would just be used to count the books...which then just having the count column is useful.  Although if a count column is used, then it is better to create a separate table for that.

 

Book Count: (ISBN, Count)

[Hikaru12]

You guys have given me different scenarios to think about. However, I don't know if I quite follow. Do you guys suggest a count table for calculating how many books are in inventory or using a calculation? I would ideally like to use the ISBN to identify the books but that does present a problem for multiple copies of the book. Which then presents a problem in itself, should I be using this unique ID I assign to each book as the identifier or the ISBN?

 

What would be easiest way to implement this? Is Access the way to go or do you guys think another database solution would be easier?

[WanderingFool]

Never really used access, so can't really say.  Personally I use mysql when I need to.

 

For the book IDs @MikeD and myself differ on opinions...it really depends on how you want to physically implement the system.

 

MikeD's solution is for cataloging each book (so each book has an unique ID).  You would get how many of a certain type of book (ISBN) like this

SELECT COUNT(ISBN) FROM BookCopy WHERE ISBN='asdfasdfasdf';

This is good if you want to lets say return the exact textbook back to the owner.

 

If lets say you have 5 copies of a book but don't care which book is which (as long as their ISBN's match), then I would recommend my layout (Where you only keep the count)

SELECT Count FROM BookCopy WHERE ISBN='asdfasdfasdf';

 

*Edit: Just wanted to add on that when constructing databases it is always good to look at your use case.

If you overlook a detail or two when creating a database it could be a messy hack to include it later (Not necessarily, and implementing a DB correctly can minimize the chances).  This is a really good example of needing to figure out the details more, before creating a database.

 

Here are a few questions which could affect the database later on:

Do you want borrows to have limited borrow time? (You mentioned borrowing for a semester, are you going by semester date end? What happens when someone enrolls in half a semester will that affect the borrow date?)

How will exchanges work? (If you exchange a book for a full semester, but are attending an half a semester will that work given that your book is still out on loan?)

Do you want to id each book? (If you do id each book, using lets say a barcoder, will the people exchanging their books wanting their book back mind if it is barcoded? If you don't id each book, what will happen if a book is all garbled?)

 

Where will the database be kept? (If it is on the internet make sure it is secured, if it is on a local machine make sure no one has access to it)

[Hikaru12]

These are some great questions and indicate how much thought has to go in designing a proper database. I'm thinking the loan date will stay the same regardless of when the student enrolls. Books that are exchanged are considered on loan for the entirety of the semester and will not account for any other situations. IDing each book would be ideal and most students wouldn't care for a tag on the outside so I don't think that would be an issue. Barcoding would be the best way to go about it.

[WanderingFool]

Ok, then I agree with @MikeD 's method

[Hikaru12]

I'll give this a shot and report back on my results.