Some non-relational data sources cannot represent a
many-to-many relationship directly. However, they can represent
it indirectly, and you can describe it as such.
Consider the EMPINFO segment in the EMPLOYEE data source and
the CLASSES segment in a hypothetical SCHOOL data source. Each instance
of EMPINFO describes one employee, and each instance of CLASSES
describes one course. Each employee can take many courses, and many
employees can take each course, so this is a many-to-many relationship.
This type of relationship is illustrated in the following diagram.
However, because some types of data sources cannot represent
such a relationship directly, you must introduce a mediating segment
called ENROLLED. This new segment contains the keys from both of
the original segments, EMP_ID and CLASS_CODE, representing the relationship
between the two original segments. It breaks the M:M relationship
into two 1:M relationships. Each instance of EMPINFO can be related
to many instances of ENROLLED (since one employee can be enrolled
in many classes), and each instance of CLASSES can be related to
many instances of ENROLLED (since one class can have many employees
enrolled).
These relationships are illustrated in the following diagram.
The next step is to make the mediating segment a child of one
of the two original segments. You can design the SCHOOL data source
so that CLASSES is the root and ENROLLED is the child of CLASSES.
Note that when ENROLLED was an unattached segment it explicitly
contained the keys (EMP_ID and CLASS_CODE) from both original segments.
Yet as part of the SCHOOL data source, CLASS_CODE is implied by
the parent-child relationship with CLASSES, and it can be removed
from ENROLLED. You can then join EMPINFO and ENROLLED together.
This type of join is illustrated in the
following diagram.
When the original M:M relationship is seen from this perspective,
it looks like a 1:M:1 relationship. That is, one employee (EMPINFO)
is enrolled many times (ENROLLED), and each enrollment is for a
single class (CLASSES).
When you report from or update the new structure at any one time,
the relationship is seen from the perspective of one of the original
segments (in this case, from EMPINFO or CLASSES). Determine which
segment perspective is used by making that segment the parent in
the join. Describe the join using the JOIN command, or for FOCUS
data sources, in the Master File. If you make the mediating segment,
in this case ENROLLED, the child (cross-referenced) segment in the
join, you implement the relationship as a standard one-to-many.
If you make it the parent (host) segment, you implement the relationship
as a standard one-to-one join.
For example, you can use the JOIN command
to describe the relationship from the perspective of the CLASSES
segment, making ENROLLED the join host:
JOIN EMP_ID IN ENROLLED TO EMP_ID IN EMPINFO
The new structure is illustrated in the
following diagram.
Another example that uses a join defined in the Master File is
illustrated by the sample FOCUS data sources EMPLOYEE and EDUCFILE.
Here, ATTNDSEG is the mediating segment between EMPINFO and COURSEG.
