PersonGroup Table

The PersonGroup table stores information that pertains to a person's relationship to a group and it resolves the many-to-many relationship between persons and groups. Linking Tables in the Eval Database describes the role of linking tables in the Eval SQL Server database. The PersonGroup table has foreign key relationships with the GradeScale, Group, and Person tables. The following illustration shows the column properties of the PersonGroup table.

• The PersonGroupId column is the primary key. Its data type is int and the Identity attribute is checked. Because a person can have more than one relationship to a group, provided each relationship is of a different type (individual, evaluator, or auditor), the combination of PersonId, GroupId, and PersonType columns is a viable alternative to using the PersonGroupId column as the primary key. Using the PersonGroupId column (a synthetic primary key) improves performance of the PT application and a UNIQUE constraint on the combination of the PersonId, GroupId, and PersonType columns enforces the business rule of one relationship between a person and a group for each person type. Constraints on Eval Table Columns describes the constraints in the Eval database.
• The PersonId column defines the foreign key relationship to the Person table. Its data type is int because the foreign key relationship requires that the data types of related columns match.
• The GroupId column defines the foreign key relationship to the Group table. Its data type is int.
• The PersonType column identifies the relationship of a person (individual, evaluator, auditor, or contact person) to a group. Constraints on Eval Table Columns describes how the data integrity of this column is maintained. The default PersonType value is I because each group is expected to have many individuals but only one or two evaluators and auditors.
• The GradeScaleId column contains the foreign key to the GradeScale table, which specifies the grade scale for a person in a group. The grade scale in the PersonGroup table must be GradeScale1 or GradeScale2 from the Group table. If GradeScale2 is null, GradeScale1 is used.