hi everybody,
I am not a computer guru but I would like to understand why Access
can’t handle a many-to-many relationship in a table.
Would your queries still work (i.e. giving you the right results) if a
third table is not used on a many-to-many relationship table?
Many-to-many relationship is quite common in real life. E.g.: a child
can have one or more parents and a parent can have one or more child.
In your explanation, I would appreciate if you could use real life
examples to demonstrate the issues and problems that you would
encounter if you do not use a third joining table.
Many thanks,
Pb
This has nothing to do with Access per se; it has to do with the basic logic
of the relational database model (which is used by Oracle, SQL/Server, DB/2,
Paradox, on and on).
In the 1950's Codd, Date and others came up with the relational model to deal
with this situation. At the core of it is the idea that data is stored in
Tables with Fields, and that a field should be "atomic" - having only one
value. An atomic field can therefore not represent many different values at
the same time!
A classic example is a class-enrollment application: each Class has zero, one
or many students; each Student can enroll in zero, one or many Classes. A
Student has attributes - last name, first name, birthdate, address, and so on;
a Class has its own attributes - class name, teacher, department, scheduled
time, etc.
But a Student doesn't have a "Class" attribute. What field could you put into
the Student table to record the information that she is taking English, Intro
to Philosophy, Calculus II and Relational Database Design? If you jam all
those distinct pieces of information into one field, it becomes very difficult
to work with. Similarly, in the table for Classes, there'll be a record for
Relational Database Design. How would you store in that record the 37 students
taking that class?
In an application like this there are not just two Entities - "Classes" and
"Students"; there are *three* - Classes, Students, and Enrollment. An
Enrollment is a record of the fact that Lisa Jenkins enrolled in Relational
Database Design (on such and such a date, perhaps with such and such a final
grade). Each instance of the relationship between a class and a student in
that class is a real-world, valid fact, which is not a characteristic of
either the student or the class, and therefore needs someplace to store that
fact.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also
http://www.utteraccess.com