Microsoft Office Tutorials and References
In Depth Information
What Are Relationships?
Don’t be surprised if you almost never create one-to-one relationships
between database tables; we hardly ever do.
For a one-to-one relationship, you need one or more fields that link the two
tables. Make sure that both tables have the same primary key field(s).
Many-to-many relationships are more complicated than one-to-one and
oneto-many relationships because a many-to-many is really two relationships in
one. Here are some examples of tables in which zero, one, or many records
in one table can match zero, one, or many records in the other:
✦ Students in courses: If you create a database to keep track of students in
a school, many students are in each class, and each student takes many
classes. You have many records in the Students table matching each
record in the Courses table — that is, many students are in each course.
You also have many records in the Courses table matching each record
in the Students table — that is, each student can take many courses.
✦ Committees: If you set up a database for a club or religious group, you
may want to keep track of who serves on what committee. One person
can be on lots of committees, and one committee can have lots of
members. The relationship between the People table and the Committees
table is many-to-many.
✦ Books and authors: One book can be written by a group of authors
(such as this topic), and one author can write many books. The
relationship between the Books table and the Authors table in a bookstore
inventory database can be many-to-many.
Figure 3-3 shows a many-to-many relationship between students and
courses. Each student is in several classes; each course has its own bunch of