Table Relationships

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

phmckeever - 03 Mar 2005 15:37 GMT

In my Book Inventory database, I have developed three tables: Books,
Authors, and Publishers.

I am trying to create one-to-many relationship between Authors and Books and
between Publishers and Books. However, when I drag the link from Authors to
Books or from Publishers to Books, I get one-to-one relationships. The
Primary Keys for Authors and Publishers tables are the Foreign Keys in Books
and are the links I am trying to use. What am I doing wrong?

HELP! :-( (New User)

Reply to this Message

Sheila D - 03 Mar 2005 17:45 GMT

Have you set the field in the Books table as a Primary Key? If both tables
have a primary key and that is the link field it will be a one to one
relationship.

> In my Book Inventory database, I have developed three tables: Books,
> Authors, and Publishers.
[quoted text clipped - 6 lines]
>
> HELP! :-( (New User)

Reply to this Message

H - 03 Mar 2005 20:53 GMT

Why would you use a one-to-one relationship between author and publisher.
Don't publishers publish many different authors work? Why would you not use a
one to many relationship? How does one know which relationship to use?

> Have you set the field in the Books table as a Primary Key? If both tables
> have a primary key and that is the link field it will be a one to one
[quoted text clipped - 10 lines]
> >
> > HELP! :-( (New User)

Reply to this Message

Van T. Dinh - 03 Mar 2005 22:17 GMT

I would recommend Many-to-Many relationship between Authors and Books. An
Author can write Many Books and a Book can be (co-) written by Many Authors.

If you use O-to-M, then you are limited to One Author per book which doesn't
reflect the real-life situations.

Check Access Help / Database books on Many-To-Many relationship.

Signature

HTH
Van T. Dinh
MVP (Access)

> In my Book Inventory database, I have developed three tables: Books,
> Authors, and Publishers.
[quoted text clipped - 6 lines]
>
> HELP! :-( (New User)

Reply to this Message

rpw - 04 Mar 2005 19:03 GMT

> In my Book Inventory database, I have developed three tables: Books,
> Authors, and Publishers.
[quoted text clipped - 4 lines]
> Primary Keys for Authors and Publishers tables are the Foreign Keys in Books
> and are the links I am trying to use. What am I doing wrong?

I think that you;ll need to review all of the relationships a little more.
One author might have many books and one book might have many authors. One
publisher might have many books and one book might have many publishers. To
handle these kinds of relationships (which are termed 'many-to-many), you
need to create another table called a junction table. Then, in the case of
the junction table between Authors and Books, the table might look like this:

tblAuthorBook
AuthBkID 'primary key, although a composite PK can be used instead
AuthorID 'foreign key
BookID 'foreign key

With this table, you can associate one author with many books and one book
with many authors.

Use another junction table for the link between Publisher and Books:

tblPublisherBook
PubBkID 'primary key
PublisherID 'foreign key
BookID 'foreign key

Hope this helps,

Reply to this Message