Group Table

All information that is common to a collection of persons or that applies to a group of activities resides in the Group table. This table has foreign key relationships with the Activity, GradeScale , Location, and PersonGroup tables. The following illustration shows the column properties of the Group table.

• The GroupId column is the primary key for an activity. The Identity attribute guarantees that a unique number is generated for each new activity added to the table. The identity seed and identity increment are 1. The first GroupId assigned is 1 and each subsequently added GroupId is incremented by 1.
• The LocationId column defines the foreign key relationship to the Location table. Its data type is int to match the data type of the LocationId column in the Location table.
• The Name and Description columns contain general descriptive information about the group. Name is the short version, which is defined as nvarchar (20), and Description is the long version, which is defined as nvarchar (50).
• The BeginDate and EndDate columns contain the date and time groups begin and end. Both columns have a datetime data type but only the begin date is required. Activity table describes how the smalldatetime data type is used. About the Formats Component explains how the PT application interprets and translates date and time data.
• The GradeScaleId1 and GradeScaleId2 columns contain the foreign keys to the GradeScale table. The presence of two grade scale columns indicates this table is not fully normalized. Because it is unlikely that a large number of grade scales would be assigned to a group, the database designers decide to violate the rules of normalization in the Group table to improve performance. Supporting many different grade scales for the Group table requires a linking table between the Group table and the GradeScale table; this table would result in added joins when the tables are queried and joins consume resources. The GradeScaleId column in the PersonGroup table contains the value from one of these two columns.
• The Period column is an optional attribute that provides a way to sequence groups that exist in the same date range and belong to one evaluator.
• The MaxSize column is an optional attribute that defines the maximum number of persons with the person type I (individual) that can be associated with the group. Triggers on Eval Tables describes how an insert trigger on the PersonGroup table enforces this business rule. If the group has a location, the maximum size (MaxSize) of the group must be less than or equal to the maximum occupancy (MaxOccupancy) specified in the Location table. Triggers on Eval Tables describes how this rule is enforced. The default value for MaxSize is 50.
• The Size column is an optional attribute that contains the count of individuals (person type I) currently associated with the group. Size must be less than or equal to the maximum size (MaxSize) of the group. Triggers on Eval Tables describes how the insert and delete triggers on the PersonGroup table maintain this count. When you create a new group the default Size value is 0.
• The MeetingDays column contains a 7-character bit pattern (zeros and ones), one bit for each day of the week. Position one in the string refers to Sunday, position two refers to Monday, and so on. A value of 1 indicates the group meets and a 0 indicates the group does not meet; for example, if a group meets Monday through Friday, the MeetingDays column contains 0111110.

Note  Size and group are Transact-SQL reserved words and you must enclose them in square brackets ([Size], [Group]) or double quotation marks ("Size", "Group") when you use them as identifiers or object names.