Please help if you have the time& patience (Setting up Relationshi

[stainless steel]

I am having a dilema with relationship(I think that's my problem).
I am trying to set up a simple database (maybe), just to keep a reading log
of books that I checked out from my local library. But not having much luck
setting up a database. I want to establish a relationship between ; BookID,
AuthorID and Author name. Is this possible?? The bookID, AuthorID and Author
name will be the same for each new book. But at the same time I understand
that one Author will have many or more than one books. I want the form to
open in Books and Author as a subform. Do I need to include AuthorID in the
book form when I am designing my form??
This is what I have been trying to do as my table:
Books Author category
BookID AuthorID Fiction
Title Author Name Non-Fiction
Date Read
Date Checked Out
Date Returned
(any of course some other minor fields)
Are there any fileds that should be included in any of the tables that I
haven't done to set up the relationship, that will make this work (If at all
it can be done as I am thinking)
Thanks a million

 

[Allen Browne]

Okay, you are asking about normalizing, so that means thinking through what
you actually need to achieve, such as:

a) Do you need to track times when you took out a book more than once? The
answer may well be No if you are reading fiction, but it might be Yes if you
are borrowing reference books. If you do need to track this, you would need
a table of books with a BookID primary key. Your table of borrowings would
then only have the BookID (not the title and author again.)

b) Do you want to analyse your borrowings by author? If so, you need a
separate table of authors with an AuthorID, and your table of borrowings
would only have the AuthorID (not the author name.)

c) A book can have multiple authors. Do you need to analyse your borrowings
across multiple authors? If so, you will need a junction table between books
and authors (since one author can write many books, and one book can have
many authors.)

d) You need a table of categories, to supply the valid values for your
Category field.

e) Are your borrowings limited to books? Or might you borrow other resources
(such as movies/documentaries/DVD lectures, or audio/CD/mp3 recordings)? If
so, you need to consider that other resources don't have authors and
publishers, but producers, directors, singers, speakers etc.

f) Do you need to handle nested resources (e.g. a compilation where each
chapter of the book is written by different authors, or
magazines/periodicals with articles by different people.) If you then need
to search for the times you have read Stephen Hawkins (regardless of whether
the thing you read was an article or a book), you will need a structure to
handle that.

Here's an example of handling all those kinds of issues:
http://allenbrowne.com/AppLibrary.html
It doesn't actually record the borrowing/lending, but that's a simple
addition to the suggested schema.

Hopefully that will help you think through what structure will be best for
your needs.

 

[stainless steel]

First of all, thanks a lot for taking the time to respond. I realized it was
a lenghty question.
The link you sent is very helpful. I am looking through it for future
project.

Now, what I am presently trying to accomplish is just a databse that will be
limited to books borrowed from the libray. Just to track all the books I
borrowed and read from the library. Just the bookID (maybe) title, the
author, the date it was checked out and the date it was returnded (and maybe
an additional thing or two like: ISBN Number and library code number, pages,
etc..) I currently have it on excel spreadsheet, but I realize it can be
easily view and entered and accessible in an access database.

Thank you