Book Exchange Database

[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!

[June7]

Have you looked at the Microsoft Lending Library template database?

I don't think switching to MYSQL will resolve the issue. Even there would have to figure out relationships and how to manage the process you describe.

You certainly don't have enough tables. First of all, you will probably have multiple copies of the same book. How many of each? Should you have a record for each book or should they be tracked like stock inventory?

Could maybe treat the movement of books coming and going like product inventory in/out transactions with transaction types (loan, trade, donate, discarded).

[hikaru12]

I would probably treat these like stock inventory and wouldn't care about individually identifying each book. I would just care about how many copies of a particular ISBN I have. Maybe I should have a field that allows me to input if the book was exchanged and what book it was exchanged for. I need to use the database for automatic pruning (deleting of books that we no longer have) and statistical analysis of how many books are traded and in what quantity so we can have a better idea of what books we should be trying to obtain are (probably mostly science and math books).

[orange]

I agree with June7 comments re tables and copies. Further, I think you should write down in point form exactly what your business rules are.
I think you are guessing at some things (wouldn't care about individually identifying each book) and (Maybe I should have a field that allows me to input...) and
(deleting of books that we no longer have).

After you create a list of business rules, I suggest you create a data model and confirm that the model reflects all of the business rules.

Good luck.