Access 2007 Tutorial 1.3. Many to Many Relationship
Uploaded by djdates on 15.11.2009
Transcript:
In the previous video, we created a simple access database. The database contained two
tables. One table was the authors table that contained the last name and first name of
authors. The second table was the books table that contained the titles of books, the year
they were published, and the author ID field which identified the author record for the
author that wrote the book. This allowed us to list books without duplicating information.
An author could write more than one book, but their name only appeared in the database
a single time.
The books table contained the author IDs for each author for the books they had written.
We quickly run into a problem with our database if we try to add a book that has two authors.
First I will add the authors. Now if I try to add both of those authors to one book,
I only have the ability to put in one author ID. I have no way of putting more than one
author ID in the author ID field. So this means I have to re-examine the way my database
is built.
You will notice the one to many relationship that exists between authors and books. This
means that one author can write many books, but it does not mean that a book can have
many authors. One way of solving this problem is creating a third database table that acts
as an association table. In our original example, we have a primary key in the authors table
that is related to a foreign key, called Author ID, in the books table. In our modified database,
we will have an association table that consists of foreign keys that relate back to both tables.
Now what I will do, is I will delete the relationship between the authors and books table by right
clicking on the relationship and selecting delete. I answer the dialog box that asks
me if I wish to permanently delete the relationship. Now the tables are no longer related to each
other. I am going to edit the books table in design view and I am going to delete the
author ID field by right clicking on the blue area to the left of the field name and selecting
delete.
Again, I am going to click on yes, but notice also this time it is deleting the data that
resides in those fields as well. Before doing something like this, I should make sure that
I have the data somewhere else. It is also asking me if I am sure I want to do this because
Access will delete one or more indexes. An index is a way of quickly finding a record
in a table. I am going to click yes to this as well.
Not that I have deleted both the relationship and the author ID foreign key in the books
table, I am going to close my tables. I am going to create new table by going to the
create ribbon and clicking on table. I am going to go to design view for this table.
I am going to call the table authored. I am going to create an author ID field, making
sure that the data type is number. I am going to create a book ID field, making sure the
data type is number. And then I will close my table, saving the changes.
I am now going to go back to Database Tools Relationships and I will click on the Show
Table icon in the Relationship Tools Design Ribbon and make sure that the authored table
is added. I may want to click and drag my tables around to reorganize them, so it is
clear to me what I am doing. I am going to drag the author ID field from the author table
to the ID field in the authors table and I am going to enforce referential integrity.
Notice that the relationship type is one to many between the author's ID field in the
authored author ID field and I will click on create. Then I will drag the book ID field
in the authored table to the ID field in the books table.
Again, checking the boxes for referential integrity and again, noticing that it is a
one to many relationship and I will click on create. Now I can see that an author can
have many authored records, but an authored record can only have one authors record. I
can see that a book can have many authored records but a single authored record can only
have one book. I am going to close out of the relationships window, saving my changes
and I am going to open the authored table and I am going to begin entering my foreign
keys. I may wish to go back to my tables to double check my work.
Now when I get to the new book that I entered that had multiple authors, I can add two records
with the same book ID and there is nothing restricting me from doing that. Notice that
both of these records with the same book ID have different author IDs to reflect the two
authors that wrote that book. If I want to see the results of my data, I can create a
query.
First, I am going to close the tables that I have open, and I will go to the create ribbon
and click on Query Design. I am going to use the control key to select all of my tables
and I will click on add. I can then click on close.
Even though my tables are jumbled, I can still move them around so that it is a little clearer
what is happening. I will drag down the information that I want to query. Notice that I do not have to query on the
foreign keys in the authored table, but because they exist, my query will work, showing me
all the authors.
I will click on the run button to run my query, and it shows me all of the authors that are
in my database, as well as the same book that is authored by two different authors. Remember
that this book only appears once in the books table and it is because of my query that it
is appearing twice when I query it, because it is coming back for each author that is
listed as authoring that book.
A common mistake when querying multiple tables is neglecting to include the table in your
query that is creating the association. I am going to edit my query and go back into
design view. Watch what happens when I remove the authored table from my query. Notice that
there is no longer an indication of a relationship between the books and authors table, and notice
that when I now run my query, I get what is called a Cartesian product. It is every possible
combination of book and author. This is inaccurate and it is happening because the query does
not know about the relationship. So if this happens, I need to make sure that I am including
the table that contains the relationship between these two tables that are otherwise unrelated.
Once I include that table, when I run my Query, I see the results that I expect.
tables. One table was the authors table that contained the last name and first name of
authors. The second table was the books table that contained the titles of books, the year
they were published, and the author ID field which identified the author record for the
author that wrote the book. This allowed us to list books without duplicating information.
An author could write more than one book, but their name only appeared in the database
a single time.
The books table contained the author IDs for each author for the books they had written.
We quickly run into a problem with our database if we try to add a book that has two authors.
First I will add the authors. Now if I try to add both of those authors to one book,
I only have the ability to put in one author ID. I have no way of putting more than one
author ID in the author ID field. So this means I have to re-examine the way my database
is built.
You will notice the one to many relationship that exists between authors and books. This
means that one author can write many books, but it does not mean that a book can have
many authors. One way of solving this problem is creating a third database table that acts
as an association table. In our original example, we have a primary key in the authors table
that is related to a foreign key, called Author ID, in the books table. In our modified database,
we will have an association table that consists of foreign keys that relate back to both tables.
Now what I will do, is I will delete the relationship between the authors and books table by right
clicking on the relationship and selecting delete. I answer the dialog box that asks
me if I wish to permanently delete the relationship. Now the tables are no longer related to each
other. I am going to edit the books table in design view and I am going to delete the
author ID field by right clicking on the blue area to the left of the field name and selecting
delete.
Again, I am going to click on yes, but notice also this time it is deleting the data that
resides in those fields as well. Before doing something like this, I should make sure that
I have the data somewhere else. It is also asking me if I am sure I want to do this because
Access will delete one or more indexes. An index is a way of quickly finding a record
in a table. I am going to click yes to this as well.
Not that I have deleted both the relationship and the author ID foreign key in the books
table, I am going to close my tables. I am going to create new table by going to the
create ribbon and clicking on table. I am going to go to design view for this table.
I am going to call the table authored. I am going to create an author ID field, making
sure that the data type is number. I am going to create a book ID field, making sure the
data type is number. And then I will close my table, saving the changes.
I am now going to go back to Database Tools Relationships and I will click on the Show
Table icon in the Relationship Tools Design Ribbon and make sure that the authored table
is added. I may want to click and drag my tables around to reorganize them, so it is
clear to me what I am doing. I am going to drag the author ID field from the author table
to the ID field in the authors table and I am going to enforce referential integrity.
Notice that the relationship type is one to many between the author's ID field in the
authored author ID field and I will click on create. Then I will drag the book ID field
in the authored table to the ID field in the books table.
Again, checking the boxes for referential integrity and again, noticing that it is a
one to many relationship and I will click on create. Now I can see that an author can
have many authored records, but an authored record can only have one authors record. I
can see that a book can have many authored records but a single authored record can only
have one book. I am going to close out of the relationships window, saving my changes
and I am going to open the authored table and I am going to begin entering my foreign
keys. I may wish to go back to my tables to double check my work.
Now when I get to the new book that I entered that had multiple authors, I can add two records
with the same book ID and there is nothing restricting me from doing that. Notice that
both of these records with the same book ID have different author IDs to reflect the two
authors that wrote that book. If I want to see the results of my data, I can create a
query.
First, I am going to close the tables that I have open, and I will go to the create ribbon
and click on Query Design. I am going to use the control key to select all of my tables
and I will click on add. I can then click on close.
Even though my tables are jumbled, I can still move them around so that it is a little clearer
what is happening. I will drag down the information that I want to query. Notice that I do not have to query on the
foreign keys in the authored table, but because they exist, my query will work, showing me
all the authors.
I will click on the run button to run my query, and it shows me all of the authors that are
in my database, as well as the same book that is authored by two different authors. Remember
that this book only appears once in the books table and it is because of my query that it
is appearing twice when I query it, because it is coming back for each author that is
listed as authoring that book.
A common mistake when querying multiple tables is neglecting to include the table in your
query that is creating the association. I am going to edit my query and go back into
design view. Watch what happens when I remove the authored table from my query. Notice that
there is no longer an indication of a relationship between the books and authors table, and notice
that when I now run my query, I get what is called a Cartesian product. It is every possible
combination of book and author. This is inaccurate and it is happening because the query does
not know about the relationship. So if this happens, I need to make sure that I am including
the table that contains the relationship between these two tables that are otherwise unrelated.
Once I include that table, when I run my Query, I see the results that I expect.