A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old September 5th, 2009, 11:59 AM posted to microsoft.public.access.tablesdbdesign
stainless steel
external usenet poster
 
Posts: 15
Default Please help if you have the time& patience (Setting up Relationshi

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
--
stainless steel
  #2  
Old September 5th, 2009, 03:29 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Please help if you have the time& patience (Setting up Relationshi

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.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"stainless steel" wrote in message
...
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
--
stainless steel


  #3  
Old September 5th, 2009, 09:47 PM posted to microsoft.public.access.tablesdbdesign
stainless steel
external usenet poster
 
Posts: 15
Default Please help if you have the time& patience (Setting up Relatio

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
--
stainless steel


"Allen Browne" wrote:

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.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"stainless steel" wrote in message
...
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
--
stainless steel



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.