Designing the HRActions Table

Designing the HRActions table is typical of the process you go through when you create a set of relational tables for almost any purpose. Rather than add fields for entries such as salary, commission rate, and bonuses to the Employees table, you should place employee remuneration data in a table of its own, for the following reasons:

• Multiple HRActions are taken for individual employees over time. If you add these actions to records in the Employees table, you have to create many additional fields to hold an arbitrary number of HRActions. If, for example, quarterly performance reviews are entered, you would need to add a new field for every quarter to hold the review information. In this situation, spreadsheet applications and flat-file managers encounter serious difficulties.

• HRActions usually are considered confidential information and are made accessible only to a limited number of people. Although you can design forms that don't display confidential information, restricting permission to view an entire table is a more secure approach.

• You can identify employees uniquely by their EmployeeID numbers. Therefore, records for entries of HRActions can be related to the Employees table by an EmployeeID field. This feature eliminates the necessity of adding employee names and other information to the records in the HRActions table. You link the Employees table to the HRActions table by the EmployeeID field, and the two tables are joined; they act as though they are a single table. Minimizing information duplication to only what is required to link the tables is your reward for choosing a relational, rather than a flat-file, database management system.

• You can categorize HRActions by type so that any action taken can use a common set of field names and field data types. This feature makes the design of the HRActions table simple.

The next step is to start the design of the HRActions table. Chapter 4 discusses the theory of database design and the tables that make up databases. Because the HRActions table has an easily discernible relationship to the Employees table, the theoretical background isn't necessary for this example.

Determining What Information the Table Should Include

Designing a table requires that you identify the type of information the table should contain. Information associated with typical human resources department actions might consist of the following items:

• Important dates The date of hire and termination, if applicable, are important dates, but so are the dates when the employer adjusts salaries, changes commission rates, and grants bonuses. You should accompany each action with the date when it was scheduled to occur and the date when it actually occurred.

• Types of actions Less typing is required if HRActions are identified by a code character rather than a full-text description of the action. This feature saves valuable disk space, too. First-letter abbreviations used as codes, such as H for hired, T for terminated, and Q for quarterly review, are easy to remember.

• Initiation and approval of actions As a rule, the employee's supervisor initiates a personnel action, and the supervisor's manager approves it. Therefore, the table should include the supervisor's and manager's EmployeeID number.

• Amounts involved Salaries are assumed to be bimonthly based on a monthly amount, hourly employees are paid weekly, bonuses are quarterly with quarterly performance reviews, and commissions are paid on a percentage of sales made by the employee.

• Performance rating Rating employee performance by a numerical value is a universal, but somewhat arbitrary, practice. Scales of 1 to 9 are common, with exceptional performance ranked as 9 and candidacy for termination as 1.

• Summaries and comments The table should provide for a summary of performance, an explanation of exceptionally high or low ratings, and reasons for adjusting salaries or bonuses.

Note

Fields containing a code for pay type salary, hourly, commission and bonus eligibility would be useful additions to the Employees table. You could use such codes to validate amount entries in the HRActions table.

If you're involved in personnel management, you probably can think of additional information that the table might include, such as accruable sick leave and vacation hours per pay period. The HRActions table is just an example; it isn't meant to add full-scale human resources application capabilities to the database. The limited amount of data described serves to demonstrate several uses of the new table in this and subsequent chapters.

Assigning Information to Fields

After you determine the types of information called data attributes or just attributes to include in the table, you must assign each data entity to a field of the table. This process involves specifying a field name that must be unique within the table. Table 5.7 lists the candidate fields for the HRActions table. Candidate fields are written descriptions of the fields proposed for the table. Data types are logically derived from the type of value described. Table 5.8 add specifics for the data types.

Table 5.7. Candidate Fields for the HRActions Table

Field Name	Data Type	Description
EmployeeID	Number	The employee to whom the action applies. EmployeeID numbers are assigned based on the EmployeeID field of the Employee table (to which the HRActions table is related).
ActionType	Text	Code for the type of action taken: H is for hired; Q, quarterly review; Y, yearly review; S, salary adjustment; R, hourly rate adjustment; B, bonus adjustment; C, commission rate adjustment; and T, terminated.
InitiatedBy	Number	The EmployeeID number of the supervisor who initiates or is responsible for recommending the action.
ScheduledDate	Date/Time	The date when the action is scheduled to occur.
ApprovedBy	Number	The EmployeeID number of the manager who approves the action proposed by the supervisor.
EffectiveDate	Date/Time	The date when the action occurred. The effective date remains blank if the action has not occurred.
HRRating	Number	Performance on a scale of 1 9, with higher numbers indicating better performance. A blank (Null value) indicates no rating; 0 is reserved for terminated employees.
NewSalary	Currency	The new salary per month, as of the effective date, for salaried employees.
NewRate	Currency	The new hourly rate for hourly employees.
NewBonus	Currency	The new quarterly bonus amount for eligible employees.
NewCommission	Percent	The new commission rate for commissioned salespersons, some of whom might also receive a salary.
HRComments	Memo	Abstracts of performance reviews and comments on actions proposed or taken. The comments can be of unlimited length. The supervisor and manager can contribute to the comments.

Tip

Use distinctive names for each field. This example precedes some field names with the abbreviation HR to associate or establish relations with field names in other tables that might be used by the human resource department.

Creating the HRActions Table

Now you can put to work what you've learned about field names, data types, and formats by adding the HRActions table to the Northwind Traders database. Table 5.8 shows the field names, taken from Table 5.7, and the set of properties that you assign to the fields. Fields with values required in a new record have an asterisk (*) following the field name. The text in the Caption column substitutes for the Field Name property that is otherwise displayed in the field header buttons.

Table 5.8. Field Properties for the HRActions Table

Field Name	Caption	Data Type	Field Size	Format
EmployeeID*	ID	Number	Long Integer	General Number
ActionType*	Type	Text	1	>@ (all uppercase)
InitiatedBy*	Initiated By	Number	Long Integer	General Number
ScheduledDate*	Scheduled	Date/Time	N/A	mm/dd/yyyy
ApprovedBy	Approved By	Number	Long Integer	General Number
EffectiveDate	Effective	Date/Time	N/A	Short Date
HRRating	Rating	Number	Byte	General Number
NewSalary	Salary	Currency	N/A	Standard
NewRate	Rate	Currency	N/A	Standard
NewBonus	Bonus	Currency	N/A	Standard
NewCommission	% Comm	Number	Single	#0.0
HRComments	Comments	Memo	N/A	(None)

Note

You must set the EmployeeID field's Field Size property to the Long Integer data type, although you might not expect Northwind Traders to have more than the 32,767 employees that an integer allows. The Long Integer data type is required because the AutoNumber field data type of the Employees table's EmployeeID field is a Long Integer. Later in this chapter, the "Working with Relations, Key Fields, and Indexes" section explains why EmployeeID's data type must match that of the Employees table's EmployeeID number field.

To add the new HRActions table to the Northwind database, complete the following steps:

1. Close the Employees table, if it's open, to return to the Database window.

2. Click the Tables shortcut of the Database window, if it isn't selected, and double-click the Create Table in Design View item. Access enters design mode, opens a blank grid, and selects the grid's first cell. The General page of the lower properties pane is empty for a new table with no fields.

3. Type EmployeeID as the first field name, and press Tab to accept the field name and move to the Data Type column. Access adds the default field type, Text.

4. Click to open the Data Type list (see Figure 5.18) and select Number. Alternatively, type N[umber] in the list. Typing characters that unambiguously match an item in the drop-down list selects the item.

   Figure 5.18. The Data Type list lets you select from one of the nine Jet/Access data types or the Lookup Wizard. If you type a text value in a Data Type cell, the value must match the first character or two of one of the entries in the drop-down list.

   

   Note

   Another selection alternative in drop-down lists is to use Alt+down arrow to open the list, press the up- or down-arrow keys to make the selection, and then press Enter.

5. Press F6 to move to or click Field Size text box in the Field Properties window. Access has already entered Long Integer as the value of the default Field Size property for a Number field.

   Note

   Whenever you create a new Number type field, Access enters Long Integer in the Field Size property as the default. Because the EmployeeID field should be a Long Integer, you don't need to set the Field Size property for this field and can skip to step 8; continue with steps 6 and 7 when you enter the other fields from Table 5.8.

6. For Number data types other than Long Integer, select from the list the appropriate Field Size value from Table 5.8, or type the first letter of one of the values of the list, such as B[yte] or S[ingle]. For Text fields, type the maximum number of characters.

7. Press the down-arrow key or click to select the Format text box, and type G[eneral], or select General Number from the list (see Figure 5.19).

   Figure 5.19. Select one of the seven standard number formats from the list or type a format string in the Format text box. The General Number format applies if you don't set the Format property value.

   

8. Press the down-arrow key, or select the Caption text box, and type ID as the caption. ID is used as the Caption property to minimize the column width necessary to display the EmployeeID number.

9. Press the down-arrow key four times, bypassing the Default Value, Validation Rule, and Validation Text properties, and type Y in the Required text box. Typing Y[es] or N[o] is an alternative to selecting Yes or No in the drop-down list.

   Note

   When entering a Text field with the Required property set to Yes, set the Allow Zero Length property value to No.

10. Press F6 to return to the Table Design grid.

    Tip

    Add descriptions to create prompts that appear in the status bar when you are adding or editing records in run mode's Datasheet view. Although descriptions are optional, it's good database design practice to enter the field's purpose if its use isn't obvious from its Field Name or Caption property.

11. Press Enter to move the caret to the first cell of the next row of the grid.

12. Repeat steps 3 through 11, entering the values shown in Table 5.8 for each of the 11 remaining fields of the HRActions table. N/A (not applicable) means that the entry in Table 5.8 doesn't apply to the field's data type.

Your Table Design grid should now look similar to the one shown in Figure 5.20, with the exception of the optional Description property values. You can double-check your properties entries by selecting each field name with the arrow keys and reading the values shown in the property text boxes of the Field Properties window.

Figure 5.20. The 12 fields of the new table fully describe any of the eight types of personnel actions defined by the ActionType codes. Adding the Description property, which can be up to 255 characters long, is optional.

Click the Datasheet view toolbar button to return to Datasheet view in Run mode to view the results of your work. Click Yes when the "Do you want to save the table now?" message opens (see Figure 5.21, top). The Save As dialog opens, requesting that you give your table a name and suggesting the default table name, Table1. Type HRActions, as shown in Figure 5.21 (middle), and press Enter or click OK.

Figure 5.21. When you change the view of a new table that doesn't have a primary key to Datasheet, these three messages appear in sequence.

At this point, Access displays a dialog informing you that the new table does not have a primary key (see Figure 5.21, bottom). You add primary keys to the HRActions table later in this chapter, so click No in this dialog.

Your table opens in Datasheet view, with its first default record. To view all the fields of your new table, narrow the field name header buttons by dragging to the left the right vertical bar that separates each header. When you finish adjusting your fields' display widths, the HRActions table appears in Datasheet view (see Figure 5.22). Only the tentative append record (a new record that Access adds to your table only if you enter values in the cells) is present. You have more property values to add to your HRActions table, so don't enter data in the tentative append record at this point. If you close the table, a message asks if you want to save your table layout changes. Click Yes.

Figure 5.22. Adjust the display width of the fields in Datasheet view so all fields appear without scrolling the window. Specifying a Decimal Places property value of 1 results in the 0.0% default entry in the % Comm field.

Creating a Table Directly in Datasheet View

If you're a complete database novice and under pressure to create database tables immediately, Access lets you create tables directly in Datasheet view. When you create a table in Datasheet view, Access displays an empty table with a default structure of 20 fields and 30 empty records. You then enter data directly into the table. When you save the table, Access analyzes the data you entered and attempts to select a data type for each field that matches the data. Creating tables in datasheet view is a shortcut that seldom produces a satisfactory result.