Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    7

    Unanswered: Help Me - Library Database!

    Hi!

    Im creating a Library database and at this moment Im 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.

    So if you have ideas send me.

    Sorry for this long letter.


    Regards,

    Koolaccess

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can check this link, which have hundreds of data models for databases. http://www.databaseanswers.org/data_models/index.htm
    I think I saw one data model for "library and books" in this site.
    ghozy.

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Before embarking on a solution (I could suggest several here), lets just be sure we understand your requirements:

    You want to record information about:
    - Books
    - Loans
    - 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

    Other requirements:
    - 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.

    Chris

    Ps I just saw ghozys posting which may of course give you what you want.

  4. #4
    Join Date
    Sep 2004
    Posts
    7
    Hi again!

    Thank you, for yours answers.

    Ghozy:
    I consulted the site and I found a conceptual data model for a Database for a Library, but I dont know to work with the system controls parameter that is there.


    Howey:
    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 Im sending my database.


    Regards,

    koolaccess
    Attached Files Attached Files

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    do you want to keep a history of loans?
    ghozy.

  6. #6
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    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).

  7. #7
    Join Date
    Sep 2004
    Posts
    7
    Hi Ghozy,

    yes, I want to keep a history of loans.


    Wazz:

    Thank you for the idea, but like I said to Ghozy I want to keep a history of loans.

    Regards.

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    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.
    ghozy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •