I’m creating a Library database and at this moment I’m having problems with the process of loan and devolutions of the books. The main rule that I have is that I only could loan one book to a person each time. And to make a loan I have to consult first if the book exists on books table and after (assuming that record exists) I have to consult the loan table.
So I star by creating two tables: one with the records of books and the other with the loans. Next, to work with loans I created a form above a query based on join of those two tables that gives me the books available, and problem is here - because at same time I have had the information of available books and based on it I want to write the loan on the loan table, this process is not working well.
About the devolution I think the process is the same, I only want to know if I could use the same loan table to do the devolution using two fields the loan date and devolution date.
Before embarking on a solution (I could suggest several here), lets just be sure we understand your requirements:
You want to record information about:
- people (?)
The constraints are:
- a person may only loan one book at a time
- a book can only be loaned to one person at a time
- you want the user to be able to create loans via a form
- when a loan is being created, you want the form to be able to provide a list of available books (books not on loan) i.e. to be select from a dropdown list of available books
What does devolution mean in the context of your library ? To me (being British), devolution means handing down power from nation government to a more local organisational level.
Also, if you could give an indication of what tables and field names you have set up that will help us to give a clear solution.
Ps I just saw ghozy’s posting which may of course give you what you want.
I consulted the site and I found a conceptual data model for a Database for a Library, but I don’t know to work with the system controls parameter that is there.
From your message I see that you understood my first message, about devolution I want to say like “I person returning back the book to the library”. Attached to this message I’m sending my database.
Hi. I have a very simple database for my CDs. For every CD I have a Yes/No field called OnLoan. It works fine for my purposes. To find out what's out on loan I created a query with criteria in the Yes/No field = Yes (tells me everything that's on loan). The opposite query would be Yes/No = No (tells me everything not on loan).
I was cheking your database and I saw in loans table you used 2 foreign keys from other tables as primary key to ensure one user loans only one book. but this will stop you to keep a loan history. since there can be only 1 book+user combination in the loan table. it won't let you enter another loan for this particular user+book combination. for example you entered;
book#3 loaned by -> user#4
after that there can be only one record for book#3+user#4 in whole table, because their combination is a primary key.
to overcome this problem you need to remove primary key restriction on those fields then either;
#1. before adding a loan, check records if the book+user combination in use (by return date is null), if you can't find, you are free to add new record or,
#2. transfer returned book loans into another table. so you keep a "loan table" and a "loan history" table.
now to continue to my advice I need you to choose one of those solutions.