Table Procedure Examples, Design, and Use › Samples from the Commonweather Database › Sample 2: Target Record Member of a Set Relationship

Sample 2: Target Record Member of a Set Relationship

Target Record

HOSPITAL‑CLAIM (unique key CLAIM‑DATE‑0430, PATIENT‑NAME‑0430 within the COVERAGE‑CLAIMS set relationship)

Set Relationships

COVERAGE‑CLAIMS (owner COVERAGE, unique key, SELECTION‑DATE‑0400 (made up of Year, Month and Day) combined with TYPE‑0400

Path

The table below identifies the primary path and unique keys that define unique occurrences of the HOSPITAL-CLAIM record:

Foreign Key for COVERAGE-CLAIMS Relationship

EMP‑ID‑0415, SELECTION‑DATE‑0400, TYPE‑040

Analysis

The owner of the COVERAGE‑CLAIMS set is COVERAGE, which you access via the EMP‑COVERAGE set relationship. The unique key for COVERAGE is SELECTION‑DATE‑0400 (day, month, year) and TYPE‑0400. This combination of fields uniquely defines an occurrence of the COVERAGE record within the EMP‑COVERAGE relationship. To refine the unique key, you must select the EMPLOYEE record type, which owns the COVERAGE record via the EMP-COVERAGE set. The unique key for EMPLOYEE is EMP‑ID‑0415. Therefore, the foreign key for the COVERAGE‑CLAIMS relationship becomes the EMP‑ID‑0415, SELECTION‑DATE‑0400, and TYPE‑0400.

To define the table procedure specification

1. Select EMPLOYEE record in the Entry Record tab. Select EMP_ID_0415 for the unique field and then click Next.
2. In the Path Record Tab, select COVERAGE for the path record and include SELECTION_DAY_0400, SELECTION_MONTH_0400, SELECTION_YEAR_0400, and TYPE_0400 for the unique fields. Click Next.
3. In the new Path Record Tab, select the HOSPITAL‑CLAIM record and CLAIM_DAY_0430, CLAIM_MONTH_0430, CLAIM_YEAR_0430, PATIENT_FIRST_NAME_0430, and PATIENT_LAST_NAME_0430 as the unique fields. Click Target.
4. In the Target Fields tab, select additional fields for the target record before you save and generate the table procedure.

No auxiliary records exist because the target record does not participate in any other set relationships

Alternative Strategy

Instead of making the HOSPITAL-CLAIM record the target, you can also add a table procedure for the COVERAGE record type. By making COVERAGE the target, you can perform INSERT processing, setting up the foreign keys required to relate the COVERAGE table to the EMPLOYEE table.

If you select COVERAGE to be the target record, then the developer of the COVERAGE table would have to make code changes to the table procedure to support the HOSPITAL-CLAIM record type. At first this may seem an odd way to retrieve the data. But from a table standpoint, this view may be more realistic. The COVERAGE record type can be combined with the HOSPITAL‑CLAIM type to form a single view of the two independent tables.

Remember that the COVERAGE-CLAIMS relationship is a multi-member set. You can combine all three member records with the owner into one complete table. To do this, the developer would have to add code into the table procedure to include all of the appropriate fields. The developer would also have to add code to determine what record type to access and how to update and insert records.