Choosing Physical Tuning Options › Physical Tuning Options for Commonweather Corporation › Refined Commonweather Corporation Database Design (For SQL Implementation)

Refined Commonweather Corporation Database Design (For SQL Implementation)

The refined data structure diagram for Commonweather Corporation (for SQL implementation) is shown below.

A review of transactions shows that all insurance information should be clustered around an employee. This can be accomplished by removing the CALC key from NON-HOSPITAL CLAIM, HOSPITAL CLAIM, and DENTAL CLAIM entities and replacing each with a unique index on NONHOSP CLAIM ID, HOSPITAL CLAIM ID, and DENTAL CLAIM ID respectively. In addition, the location mode of each of the three entities must be changed to CLUSTERED through its relationship with COVERAGE.

Due to the volume of data in the INS DEMO REGION, it is decided that all linked relationships between this region and the EMP DEMO REGION be converted to unlinked. The only relationship affected is EMP-COVERAGE. In order to convert it to unlinked, you must either add an index or CALC key on EMP ID (the foreign key of the relationship).

Since you want to cluster coverage entity occurrences by employee anyway, a CALC key on EMP ID is chosen since it achieves the same results as clustering through the EMP-COVERAGE relationship and eliminates the need for an additional index.