Table Relationships

[phmckeever]

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)

 

[Sheila D]

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.

 

[H]

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?

 

[Van T. Dinh]

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.

 

[rpw]

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?

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,