So far, this book has discussed models for handling much of the information an enterprise needs to conduct business: parties, products, orders, shipments, invoices, work efforts, and accounting. One critical section of the business remains for discussion: human resources, or HR. Without human resources, an enterprise cannot employ and use the key resources it needs to stay in business.
Information that an enterprise may want to keep includes the following:
- Who is employed, and what is the history of employments?
- What positions exist in the company?
- Are they filled? If so, who has what position, and what are his or her responsibilities?
- Who reports to whom?
- What is the rate of pay for these positions?
- Who received raises and when?
- What benefits does the enterprise provide and to whom?
- What is the cost of these benefits?
- What is the status of employment applications?
- What are the skills of employees?
- What is the performance of employees?
- What are the preferences, deductions, and payroll information needed to process payroll?
- What applicants have there been, and how many of them have turned into employees?
- What has been the rate of turnover and the causes of turnover?
Why is it necessary to model human resource data given that enterprises usually buy a standard human resources package with its own data structure? One reason is that it is important to know what the information requirements are for the enterprise in order to drive a proper package selection. Another reason is that many enterprises implement a separate HR system, very often through a package that may not integrate well with other in-house systems.
This chapter illustrates a model that allows an enterprise to track basic human resources information and have it tied to other models presented in this book. The models presented in this chapter include the following:
- Standard human resources model (EMP DEPT model)
- Employment
- Position definition
- Position type definition
- Position fulfillment
- Position reporting
- Position fulfillment and tracking
- Salary determination and pay history
- Benefits tracking
- Payroll information
- Employee application
- Employee skills and qualifications
- Employee performance (alternate model also provided)
- Employee termination
Standard Human Resources Model
A very basic model for employees, shown in many textbooks, is called the EMP DEPT model. Figure 9.1 shows this model. Each EMP (employee) has information such as his or her employee id (emp id), emp name, position, and date hired. Each EMP reports to another EMP, who is the manager, as shown in the recursive relationship from EMP to EMP. Each EMP is within a DEPT, which also has an ID and description.
Figure 9.1 Standard emp dept model.
Figure 9.1 is useful to demonstrate data model principles by using this simplified model of human resources. The models shown in this book will illustrate a much more effective human resources data model to maintain real-life human resource information requirements.
There are several simplifying structures in this standard model:
- Employee, manager, and departments are roles that should be ideally maintained as a subtype of PARTY ROLE so that a person or organization is not redundantly stated for each role played.
- The employment of an individual to an internal organization is an important PARTY RELATIONSHIP that is not shown.
- Positions are shown as an attribute in the standard model. POSITION is an important entity, which has its own information as well as a many-to-many relationship with the employee (this will be explained later).
- The reporting structure showing one employee reporting to one and only one other employee is overly simplistic. For one thing, an employee may report to more than one manager through a dotted line or matrix structure. Another issue is whether the reporting structure really is from one employee to another. If John Jones gets promoted, do all the people that John had reporting to him also get promoted at that time? Is the reporting structure based on the person, or do positions really report to other positions?
- There is no history reflected in these data models. When did each employee have each position? When was he or she in each department? When was he employed with the organization?
The following universal data models for human resources will handle these questions and provide for a much more robust human resources data structure.
What data structure could one use to model the employee-employer relationship? There is a from date and a thru date for the relationship, there is a status of the relationship, there may be associated agreements within the context of the relationship and communication events within the relationship. These all represent information requirements for any relationship between two parties, or in other words, a PARTY RELATIONSHIP.
Figure 9.2 provides a data model to maintain employment information. EMPLOYMENT is a subtype of PARTY RELATIONSHIP and represents a relationship between the PARTY ROLES of EMPLOYEE and INTERNAL ORGANIZATION. This assumes that the enterprise is interested only in tracking its own employees. If the model needs to accommodate employees of external organizations as well, then the EMPLOYMENT entity should be between the EMPLOYEE and EMPLOYER instead.
Figure 9.2 Employment.
The EMPLOYMENT inherits the properties of PARTY RELATIONSHIP because it is a type of relationship between two parties. It inherits the fact that it has a from date, which would represent the start of the relationship or the hire date, as well as the thru date, which would be the last date of the employment. It inherits the relationship to PARTY RELATIONSHIP STATUS TYPE because each employment will have a status such as “active,” “inactive,” “pending,” “terminated,” and so on.
The EMPLOYMENT entity allows important pieces of information to be placed where they belong. For example, many data models will show the status of the employee when the data really represents the status of the employment. The status of “resigned” is not really the status of that employee. That employee may have resigned from one subsidiary and then been hired by another subsidiary within the enterprise. The status of resigned is really the status of the EMPLOYMENT because it represents a piece of information about two parties, namely that the employee resigned from an internal organization.
This provides yet another example of the importance of distinguishing between attributes of the party versus those of the relationship (two parties). When these two entities get mixed up, (and they will get mixed up if there are not two different entities in the data model from which to reference a party versus a relationship), data inconsistencies and errors occur, leading to inaccurate data.
What does a position represent? A POSITION represents a job slot in an enterprise that can be occupied by more than one person over time. In some enterprises, this may also be referred to as an FTE or full-time equivalent. For example, if the HR department of an organization was told that there were two positions (two FTEs) open for a junior programmer, that would indicate that they could hire two people. Each of these people may perform the same type of duties, but they would be filling two separate job slots. Over time, one of these people may resign and be replaced by a third person. This third person would fill the existing position that had been vacated. This would not constitute a new position. (This will be explained further in the section on position fulfillment.)
In Figure 9.3 the entities used to define a position are modeled. The POSITION entity contains the basic information to track about any given job slot. The POSITION TYPE entity provides information for further defining and categorizing a job, while the BUDGET ITEM provides a possible means of authorizing a slot. The entities RESPONSIBILITY TYPE, VALID RESPONSIBILITY, and POSITION RESPONSIBILITY provide a mechanism for assigning and tracking what are the possible and actual responsibilities for any position.
Figure 9.3 Position definition.
Some of the data an enterprise may wish to track about a POSITION is shown in Figure 9.3. The estimated from date and estimated thru date can be used for planning. This data is the first indication of when the enterprise expects to need a person to fill this role in the organization. If the position is for an indefinite period, the estimated thru date would remain blank.
The various flags included in the entity help to define the particular circumstances under which a person hired for a position will be employed. Will the person be salaried or hourly? Is the position exempt or nonexempt under the Fair Labor Standards Act (FLSA)? Will this be a full-time or part-time position? Is it temporary or permanent? The answers to these questions will be very important for benefits administrators and payroll personnel. Additionally, the model includes an actual from date and actual thru date to allow tracking of this data once it is known. Sample data for some of these attributes is shown in Table 9.1.
Table 9.1 Position Data
First, for a job to even exist in an enterprise, it must usually be approved and funded in some manner. In this model, positions can be approved through a BUDGET ITEM. One BUDGET ITEM may approve or fund one or more positions. For example, there may be a budget item for programmers. The amount of this budget item is then used to fund the multiple programmer positions that the enterprise has. If a position is tied to a budget, then the position is considered “authorized” when the BUDGET STATUS for the BUDGET that affects BUDGET ITEM has been approved (see Chapter 8 for more on the BUDGET model).
Even though each job opening represents a single occurrence of POSITION, several of these openings could have some characteristics in common, such as a title or description of the type of job. Those common characteristics are represented by a common POSITION TYPE. The entity POSITION TYPE maintains information associated with all the slots that exist for a kind of job. Table 9.2 contains examples of this data.
Table 9.2 Position Type Data
As shown in the data, each POSITION TYPE will have a position type ID for identification, a brief description, and a standard job title. Other data that could be stored is the benefit percent, which records the percentage of benefits that an enterprise will pay for a particular type of position. For example, the data in Table 9.2 indicates that the enterprise has made a decision to pay 100 percent of the benefits for all “business analyst” positions.
Also shown in Figure 9.1 are the entities RESPONSIBILITY TYPE, VALID RESPONSIBILITY, and POSITION RESPONSIBILITY. These will allow for the definition of various job responsibilities, identification of which responsibilities are appropriate for the different position types, and identification of responsibilities that are actually assigned to a given position. Notice that both VALID RESPONSIBILITY and POSITION RESPONSIBILITY have a from date and a thru date. These allow the enterprise to assign and track historically changing responsibilities for jobs and positions. In this way very specific and detailed job descriptions can be developed, while at the same time allowing for ongoing change.
To maintain a tightly integrated system, the enterprise may wish to establish business rules to control some of this data. Data checks could be developed to ensure that, if a certain RESPONSIBILITY TYPE is assigned as a POSITION RESPONSIBILITY, it is first identified as a VALID RESPONSIBILITY for the POSITION TYPE with which the POSITION is associated. For example, a new position for an “account representative” is created. One of the responsibilities is to “create monthly sales report.” Before that could be assigned, “create monthly sales report” would have to already exist as a VALID RESPONSIBILITY associated with the POSITION TYPE of “account representative.” In addition, the from date and thru date in both entities could be compared to be sure that the assigned responsibility is actually valid for the assigned time period.
Some enterprises may need to further classify the types of positions they have. To help with this, the model shown in Figure 9.4 was developed. In it an optional entity POSITION TYPE CLASS is shown as an intersection between POSITION TYPE and POSITION CLASSIFICATION TYPE. Using these entities, additional groupings of types of positions can be done. Because it is possible that a POSITION TYPE could be reclassified over time, the POSITION TYPE CLASS entity with its attributes of from date and thru date are included to support the many-to-many relationship between POSITION TYPE and POSITION CLASSIFICATION TYPE. An example of possible data is shown in Table 9.3
Figure 9.4 Position type definition.
Table 9.3 Position Type Classification Data
The data shown in Table 9.3 indicates that for the enterprise in question, the position types “programmer,” “system administrator,” and “business analyst” were at one time all classified as “computer” positions. Then in 2000, the “business analyst” was reclassified to “MIS.” In addition to the “computer” classification, “programmer” is also classified as “technical” while the “system administrator” is considered “admin support.” In any of the cases where the thru date is not included, it is assumed that the classification is still valid. Using this model, the enterprise can develop a very detailed and flexible classification system to meet its needs.
Some enterprises may wish to use the POSITION CLASSIFICATION TYPE entity to categorize how the position will offer pay. POSITION CLASSIFICATION TYPEs could include data such as “hourly,” “salary,” “exempt,” “non-exempt,” “part-time,” “full-time,” “regular,” or “temporary.” Then, the entity POSITION TYPE CLASS would act as an historical list of these base classifications for a POSITION. Note that the POSITION entity currently shows binary attributes for pairs of these classifications. If this use is implemented with the listed data, then these attributes could be removed from POSITION. (In a physical model the enterprise may also want to implement this data as flags associated with a position slot for easier reporting of current data.)
Certain enterprises may need to track whether certain types of positions are unionized. This is handled through the relationship to UNION, which is a subtype of ORGANIZATION ROLE because a union is one type of organization. This information could be of vital importance during salary and benefits negotiations or in the case of a pending strike. If a position is protected through a union relationship, then the enterprise may have certain guidelines regarding these types of positions.
Position Fulfillment and Tracking
Figure 9.5 shows a model that will allow the enterprise to track each person's position history within the organization. It includes entities for tracking POSITION FULFILLMENT and POSITION STATUS TYPE. The model needs the entities POSITION and PERSON for context. ORGANIZATION is also included to provide information on the hiring company.
Figure 9.5 Position fulfillment.
A person can, of course, occupy more than one position either over time or at the same time. Conversely, a position may be filled by more than one person over time (and even at the same time through job sharing). The POSITION FULFILLMENT entity provides a very flexible way to retain the history of this activity. The attributes from date and thru date will allow the enterprise to keep historically accurate information about this data. It is a convenient and effective way to resolve the many-to-many relationship that really exists between PERSON and POSITION. Some possible data is shown in Table 9.5.
The data given shows the career path of Mike Johnson as he moved progressively up the corporate ladder from the position of “mail clerk” to “CEO.” Because the thru date for the “CEO” entry is blank, it is assumed that he currently holds that position. Also shown is the career of Sue Jones. She started in the position of “programmer” in 1995 then in 1997 took on the additional role of “business analyst.” She was then promoted to “MIS manager” in 1999. Finally, she earned her current position as “CIO” (Chief Information Officer) on November 2, 2000.
A popular trend in many enterprises is the concept of job sharing. In this situation, two people fill one position. Both work part-time and share the responsibilities of the position. This is where the concept of an FTE (full-time equivalent) is most useful. With a job-share situation, the enterprise has authorized only one full-time position (one FTE), which constitutes one “head count.” One half-time person plus another half-time person equals one FTE. The number of FTEs is the information needed for resource allocation, budgeting, reporting relationships, and such. Again, the information is related to or dependent on the position, not the person.
Another feature of the POSITION FULFILLMENT entity is that it can be used to record employee assignments in a job-sharing situation without confusing the FTE or head count. This is due to the fact that the primary key of this entity is the combination of an inherited key from POSITION (position ID), an inherited key from PERSON (party ID), and the attribute from date. With this three-part key, it is possible to record more than one person as having accepted the same position during the same time period. Of course, whether this is allowable could be controlled by implementing some business rules. If an enterprise never does job sharing, then the model could be changed to enforce this by removing the relationship to PERSON and hence removing the party ID from the compound key. If that were done, then the model would allow one and only one person to occupy a position during the same time period (even in this case, some business rules will be needed).
Note that it is also possible that a person from outside of the organization could fill a position that was authorized, such as a contractor. Because this model shows that POSITION FULFILLMENT must be accepted by a PERSON and not an EMPLOYEE, it is much more flexible than standard models and can handle this situation. To determine if the person is an employee or a person external to the enterprise the PARTY RELATIONSHIP entity may be used (refer to Chapter 2). Because it contains this information, PARTY RELATIONSHIP could also be used to enforce business rules such as whether positions may be filled by non-employees.
The POSITION STATUS TYPE identifies the current state of a position. When a position is first identified, it is in a state of “planned for.” When the enterprise decides to pursue fulfillment of the position, it may then change to a state of “active” or “open.” If the enterprise then decides that it no longer needs that position, the status may be “inactive” or “closed.” A “fulfilled” status would not be a value because this information can be derived from the POSITION FULFILLMENT entity.
What is the hiring company for the position? This is easily tracked via a relationship between POSITION and ORGANIZATION. Without the enterprise doing the hiring, there would be no need for a position.
Some other information not obvious in this model concerns how a person actually gets offered an open position. In fact, the interview process can be tracked through the PARTY COMMUNICATION EVENT model (see Figure 2.12). An interview is a COMMUNICATION EVENT PURPOSE TYPE with interview notes stored in the entity COMMUNICATION EVENT. See Chapter 2 for more details on this model.
By using the POSITION FULFILLMENT model, combined with the model for position reporting, an enterprise can retain and access a complete picture of its organization and structure at any point in time.
Position Reporting Relationships
Most data models show people reporting to and managed by other people. As discussed in Chapter 2, common models for people and organizations are generally oversimplified. Many models show an EMPLOYEE entity with a recursive relationship for the manager. In a large and dynamic organization, this structure could result in a lot of updating and possible data inconsistencies.
The problem is that in reality the reporting structure is a function of the organizational structure of the enterprise, not of the people in the positions. If it really was a function of the person, then when a person gets promoted, everyone who was previously reporting to that person would still be reporting to that person after the promotion. This is usually not the case. In most instances, when a supervisor is promoted, the vacated supervisory position is filled by another person. The people who originally reported to the supervisor now report to a new person, but they still report to the same position. Instead of changing everyone's reporting relationships, the position reporting model handles this situation by changing one person's position assignment (further demonstrated later in the chapter).
Additionally, an enterprise may identify its position hierarchy before assigning people to the various positions. Certainly this is the case when an organization is first created. This situation also occurs when enterprises reorganize.
What happens when the simplified model is implemented is that usually only a picture of the organization structure at the current time is maintained. All history of reorganizations and promotions is lost. This can be a serious problem when, for example, an EEOC suit is filed against the enterprise and it becomes necessary to determine who occupied a position at a particular point in time and who was the supervisor. The model presented in Figure 9.6 solves this and other challenges.
Figure 9.6 Position reporting relationships.
The model in Figure 9.6 shows the entity POSITION REPORTING STRUCTURE, which links POSITION back to itself. The attributes from date and thru date are provided to allow for tracking organizational changes through time, as previously discussed. The primary flag attribute is included to help model flexible, matrix-type structures. In these cases, certain positions may report to more than one position at the same time. This indicator allows the enterprise to indicate which reporting relationship is the overriding one.
In the examples shown in Table 9.4, business analysts, system administrators, and programmer/analysts report to the director of IS. Suppose that the enterprise grows and subsequently creates an IS development manager position and a maintenance manager position that report to the director of IS. Instead of changing the reporting relationship of each of the people, the old reporting relationships of the positions would be expired (using the thru date) and new records could be added to show the revised structure. In this way, the enterprise can more easily implement the new reporting structure and at the same time retain an accurate history of previous structures.
Table 9.4 Position Reporting Relationship Data
Table 9.5 Position Fulfillment Data
Notice that in the sample data, the current reporting relationships are identified where the thru date is blank. Alternatively, a thru date in the future could also indicate a current relationship with a preplanned end date. Note also that with the new structure, the “programmer/analyst” and “systems administrator” positions now report to both the “IS development manager” and the “maintenance manager” (these positions represent actual slots within the enterprise and not position types). They have split duties. In the case of the “systems administrator,” the primary flag is set to “yes” for the relationship to the “maintenance manager”; it is the opposite for the “programmer/analyst” position. In this way it is possible to identify to which position each of these ultimately reports.
In larger enterprises, these changes to organization structure may affect hundreds or thousands of people. By designing a system that merely changes the reporting relationship of the position instead of the actual parties involved, fewer updates will be required and presumably data will be much cleaner in the long run.
The point is that positions and people are separate entities. The next section will discuss how people and positions are actually related.
Salary Determination and Pay History
The model presented in this section is an extended compensation model that handles both highly structured organizations such as the government or less structured organizations such as small private businesses (see Figure 9.7). This is done using the entities POSITION TYPE RATE, RANGE TYPE, PERIOD TYPE, PAY GRADE, and SALARY STEP. In addition, this model also includes entities and relationships to allow the tracking of salary history (i.e., PAY HISTORY and EMPLOYMENT).
Figure 9.7 Salary determination and history.
The POSITION TYPE RATE and RATE TYPE entities were defined in Chapter 6 (see Figure 6.7) to provide the costs and standard rates for various types of positions in order to capture appropriate cost estimates for work efforts. This section will expand the use of this entity to incorporate other types of position rates for tracking standard pay rates and ranges.
The POSITION RATE TYPE entity may be used to record the allowable or acceptable salary and salary ranges for a particular position type. This information could be used by managers during the hiring process when negotiating salary. A from date and thru date are included so that a history of these standards can also be kept.
The relationship to RATE TYPE provides the ability to record reference information such as “highest pay rate,” “lowest pay rate,” “average pay rate,” and “standard pay rate,” which represent different types of pay rates that can be captured for each type of position. This would indicate such things as the upper limit, average amount paid, lower limit, and the default, standard amount of pay for a type of position. The relationship to PERIOD TYPE will allow an enterprise to define various pay period types for which rates can be recorded. Examples of PERIOD TYPE include “per year,” “per week,” “per month,” and so on. This relationship is part of the primary key to allow an enterprise to record information for multiple period types (i.e., average per hour and average per month). Table 9.6 contains sample pay rate data.
Table 9.6 Rate of Pay Data
The sample data indicates that the enterprise in question has updated rate information for the position type “programmer” only once in 10 years. The rate of pay stayed the same from 1990 through 1999, then was increased in 2000. This organization kept information on the average, high, and low annual salary that it was willing to pay to programmers. It also kept track of a standard rate that was used to establish hourly pay.
Additional information stored in this entity could also include a PAY GRADE and SALARY STEP for use in enterprises that have a predefined, highly structured pay system (such as the federal government). This is done by reference to a structured pay schedule. These types of schedules normally have two levels: a grade and a step. The SALARY STEP entity includes an amount attribute and is generally described in the context of a PAY GRADE. Table 9.7 includes part of a sample grade schedule.
Table 9.7 Pay Grade System Sample
Notice that there is an overlap in the pay scale between “GG-1” and “GG-2.” Step #1 for GG-2 falls between step #3 and step #4 of GG-1. This is not uncommon in these types of pay systems. It allows HR administrators some flexibility in negotiating pay for new employees. Because the grades are basically “set in stone” and tied to specific position types, the administrators are restricted as to what grades can be offered for any given position. To compensate for these restrictions, the range of pay covered by the steps of a grade are often very wide. Table 9.8 gives sample data for POSITION TYPE RATE where a grade system is used.
Table 9.8 Rate of Pay Sample #2
There are several things to notice in the data shown in Table 9.8. First, there are no values in the POSITION TYPE RATE rate column because, when using a pay grade system, the amount is taken directly from SALARY STEP. Business processes would, of course, need to be implemented to ensure that rate was not filled in; otherwise, there could be conflicting information. In implementing this model for use with a pay schedule, this attribute could be dropped. Conversely, when implementing the model for an enterprise that will not use a schedule, the POSITION TYPE RATE rate attribute should be mandatory.
Second, note that the thru date is also blank. This illustrates the concept that, over time, the dollar amount for a selected pay grade and step may increase, but the step assignment related to the POSITION TYPE RATE does not need to change. In this case, nothing needs to be changed in the pay rate records to indicate the increase in pay. In other words, there is no need to enter a thru date, then create a new record to show the pay increase because the pay increase is reflected via an update to the SALARY STEP information.
Actual salary or pay, represented by PAY HISTORY, is related to a person, not POSITION or POSITION TYPE. In fact, it is really related to the EMPLOYMENT (which is a subtype of PARTY RELATIONSHIP) that exists between the employer and employee.
Salary is not related to POSITION because the position the person occupies can change over time, but that doesn't mean the salary will automatically change as well. Sometimes, a person is given more responsibility and placed in a higher-level position but is not given the salary raise until he or she demonstrates the capability of performing the duties appropriately. Also, consider again the job-sharing scenario. If the pay were tied to the position, both people would have to be paid the same. This would limit the flexibility of the enterprise to pay one party more than the other based on differing levels of skill or experience.
Depending on the nature of the enterprise, salary is represented by actual dollar amounts and optionally by the relationship to SALARY STEP. Unlike the POSITION TYPE RATE attribute of rate, amount is always recorded in PAY HISTORY to ensure that there is no confusion on what the person was paid during a given time period. Also, note that because this is a record of the actual rate of pay for a person, not a list of possible rates, there can be only one record for the person for any selected time period. The PERIOD TYPE associated with this record is determined by how the enterprise wants to see this data. See Table 9.9 for examples of this data.
Table 9.9 Pay History Data
The data shown gives the record of pay for John Smith, who works for ABC Corporation. It shows his salary and increases over the past six years. Where the thru date is missing, assume that this indicates his current annual salary. With this kind of data, an enterprise can accurately track the salary history of its employees. If it is necessary to track the pay of outside parties, such as contractors, then the data model should relate PAY HISTORY to the supertype of PARTY RELATIONSHIP instead of the subtype EMPLOYMENT.
Benefits Definition and Tracking
In addition to salary or pay, most enterprises provide compensation through a benefits package. This could include vacation, health or life insurance, sick leave, or a retirement plan. The cost of these benefits may be partly or completely absorbed by the enterprise. Figure 9.8 demonstrates a simplified model for benefits tracking. It includes the information on the PARTY BENEFITs for each BENEFIT TYPE within an EMPLOYMENT for each PERIOD TYPE.
Figure 9.8 Benefits tracking.
Similar to PAY HISTORY, PARTY BENEFIT is also related to the EMPLOYMENT subtype of PARTY RELATIONSHIP, not the PARTY or PERSON, because the benefits are associated with a particular employer and employee relationship. In a large multicompany enterprise people may move from one company to another, so sometimes their benefits may come from different organizations at different times in their career. If the enterprise wants to track benefits costs at the lower levels of the organization, then associating the costs simply with the employee will not be sufficient. Why not associate the benefit with the employee and the organization directly? By using EMPLOYMENT, the enterprise can enforce business rules that would prevent such things as contractors accidentally being given benefits.
For insurance purposes, there may be a need to determine the benefits for a person for more than one organization (through the EMPLOYMENT instances for that person). Some of its employees may have two part-time jobs; it may be important to know the benefits offered by each job to avoid duplication of benefits (a coordination of benefits issue). This is also useful for determining which insurance policy should pay for an illness. The information could be critical in helping the insurance company control its costs.
In PARTY BENEFIT there can be several pieces of information that the enterprise may wish to track. First is the from date and thru date. These allow the tracking of benefits through time. Additionally, the enterprise may want to track the actual cost of the benefit and the actual employer paid percentage. With that information it would be possible to calculate the cost not only to the employee but to the enterprise as well. Another attribute, available time, is also included for tracking allowable time off such as vacation and sick leave. Example benefit data is shown in Table 9.10.
Table 9.10 Party Benefit Data
The examples given deal with John Smith, who works for ABC Corporation. Over the years, the cost of his health insurance has risen from $1,200 to $1,500 per year. During that time ABC Corporation initially paid 50 percent of the cost for him, but it now pays 60 percent. The data also shows that he has 15 vacation days and 10 sick days as a current balance and that the company absorbs the full cost of that time off. Mr. Smith also has a 401k plan that he started when he was eligible in 2001. The information indicates that the plan cost is $50 per year (for administration), with the company picking up 100 percent of the cost.
Notice that Table 9.10 includes Period Type. This is the result of the resolution of the relationship to the entity PERIOD TYPE (see Figure 9.8). This information is used primarily to modify the cost attribute. Without it, there would be no context for the dollars reported and thus no way to determine the enterprise's costs accurately. Additionally, it could also be used as the context for types like “vacation” and “sick leave,” as shown in the table.
The various types of benefits provided by the enterprise are listed in the entity BENEFIT TYPE. Samples of that data are included in Table 9.10. In addition to the identification of the benefit, this entity may also store a standard employer paid percentage that can be used to calculate costs related to all employees with a particular benefit.
Percentages for employer contribution are included not only in BENEFIT TYPE but also in PARTY BENEFIT and POSITION TYPE to allow recording this information at various levels of detail. Because of this, certain business rules need to be put in place. A likely set of rules would be as follows:
- If actual employer paid percent exists in PARTY BENEFIT, that number takes precedence.
- If that is blank, then benefit percent on the POSITION TYPE associated with the person's current position (as indicated in POSITION FULFILLMENT) is used.
- If both of these values are blank, then the employer paid percentage in BENEFIT TYPE would be the override.
Note that this section has provided a general data structure for handling benefits; if a more comprehensive model is needed for enterprises in the insurance industry, there is a more complete model in Chapter 5 of Volume 2- “Insurance.”
Another item to consider for a standard human resources model is payroll (see Figure 9.9). Without payroll, there won't be many human resources working for the enterprise for long! This model considers what to many people will be the most critical portion of payroll: getting paid correctly. Information about payroll is included in the entities EMPLOYEE, INTERNAL ORGANIZATION, PAYMENT METHOD TYPE, PAYROLL PREFERENCE, PAYCHECK, DEDUCTION, and DEDUCTION TYPE.
Figure 9.9 Payroll information.
PAYCHECK is a subtype of the DISBURSEMENT entity and is modeled separately because of some of the unique aspects of a paycheck as opposed to other disbursements. The payment model in Chapter 7 describes a generic data model for making any type of payments, and this section elaborates on that model by adding the specifics of payroll payments.
As with benefits and salary, all payroll information is related to the EMPLOYEE and INTERNAL ORGANIZATION. These relationships represent more specific modeling of the relationships of PAYMENT from and to PARTY. The relationships are mandatory because if there were no employer (internal organization) and employee or similar relationship, there would be no need for a paycheck. By using the relationships as a base, business rules can be used to ensure that not just any party within the system can be issued a paycheck.
In today's world of convenience and electronic commerce, getting paid is not as simple as it once was. Now employers can choose many options to offer their employees when it comes to methods of receiving their pay. The basic form of these options is described by the entity PAYMENT METHOD TYPE, as described in the payment models section of Chapter 7. In it could be stored data such as “cash,” “check,” and “electronic.” Depending on the capabilities of an enterprise's payroll department, any or all of these options may be offered to employees.
It is possible that an employee may want part of his or her pay in check form and the rest in cash. Others may want their money split up and electronically deposited to several different banks. Some employees may want standard deductions to be accounted for each and every paycheck, unless overridden.
In order to handle this type of information, the model includes the entity PAYROLL PREFERENCE. Because an employee may change preferences over time, the attributes from date and thru date are included. Other information that is needed includes the percentage of total pay or a flat amount that the employee wants designated to a particular PAYMENT METHOD TYPE or designated for certain recurring deductions that are maintained through the relationship from PAYROLL PREFERENCE to DEDUCTION TYPE. If the type of pay selected is “electronic,” then the routing number, account number, and bank name may be used to successfully complete the transaction. These are stored as attributes instead of as entities because the enterprise probably does not have the will and means to maintain these as their own entity. Each PAYROLL PREFERENCE may be defined for certain PERIOD TYPEs. For example, a particular standard deduction that is desired may be specified for pay period types of “per year,” “per month,” or “weekly.”
Sample data is included in Table 9.11.
Table 9.11 Payroll Preference Data
As in previous examples, assume that the employer is still ABC Corporation and the employee is John Smith. The sample data contains what is a very common scenario that payroll departments encounter today. When Mr. Smith starts with the company, he chooses to have his paycheck deposited electronically. As indicated in the data, he wants it split between a checking and a savings account. Then in 1999, he decides to close one checking account and open a new one at a different bank. The payroll department is informed, and the first preference is then expired, and a new one is entered to take effect on November 2. The fourth row shows that there is a standard deduction for insurance each month.
Note that the primary key for PAYROLL PREFERENCE is a simple sequence number compounded with the primary keys from EMPLOYEE and INTERNAL ORGANIZATION ( the hiring firm). This is needed because, depending on the type of payroll preference, different information will be recorded in the PAYROLL PREFERENCE entity.
Now that method of payment has been established, it is time to consider the actual payment itself. The PAYCHECK entity contains basic information an enterprise needs to record about the checks it writes to its employees. As seen in Figure 9.9, this entity inherits the attributes it needs from PAYMENT, namely a payment ID, payment ref num (could be a check number or electronic transfer number), effective date (date of check or electronic transfer), amount, and optionally a comment.
Financial account information for a check may be determined after depositing a check, using the payment financial account data model in Figure 7.9 in Chapter 7. This information, along with the payment ref num, will contain the information to uniquely identify a particular paycheck and to know where it has been deposited. Even electronically deposited checks need a source account and a check number, which show up on the paper confirmation. The effective date is the actual date the payroll disbursement was issued. In the case of electronic deposit, this may or may not correspond to the bank posting date. The amount is recorded as part of the PAYMENT entity and inherited by the subtype PAYCHECK as the gross amount of pay. The net amount of the paycheck can be calculated by subtracting from the PAYCHECK amount, the amounts recorded in the associated instances of the DEDUCTION entity.
The DEDUCTION entity stores information about the various deductions that occur on a particular check. The DEDUCTION TYPE entity contains a list of the valid types of deductions that are allowed by the enterprise or are required by law. Some of these include: “federal tax,” “FICA,” “state tax,” “401k,” “retirement,” “insurance,” or “cafeteria plan.”
DEDUCTION TYPEs may be applied to the actual payroll disbursements when they are related to DEDUCTIONs. These DEDUCTIONs represent instances of moneys deducted from actual payroll checks (or electronic transfers). DEDUCTION TYPEs may also be used to provide standard deductions that employees want on an ongoing basis.
Table 9.12 contains examples of deductions as they have been applied to a particular paycheck.
Table 9.12 Payroll Data
Again, assume that the sample data is related to John Smith and ABC Corporation. The data shows an example that would be typical in most organizations. The check number 10001 is cut on January 1, 2001, with a gross amount of $2,000. The deductions include the standard ones that everybody sees, plus an additional deduction for insurance. The total deductions add up to $459.50, making the net amount for the check $1,540.50
So where did the $1,540.50 go? It should be deposited or distributed based on what was recorded in the PAYROLL PREFERENCE occurrence that is associated with the same EMPLOYEE that the PAYCHECK is associated with (i.e., if a paycheck is issued for John Smith at ABC Corporation, then the preferences for John Smith at ABC Corporation should be used). If there were no preference records, then what happens? Is a paper check cut by default, or is the check processing held up until preferences are declared? Again, more business rules need to be in place to make these decisions and ensure that the entire transaction is completed correctly.
Each employment may have started with an employment application. Applications may be maintained in order to ensure that they are properly reviewed and processed. Also this information can provide valuable analysis of the recruiting process, yielding information such as how many applications are received, from where are they received (the source of the application), and the ratio of applications that turn into employees.
Figure 9.10 provides a data model that shows information about employment applications. Each EMPLOYMENT APPLICATION may be for a POSITION. This relationship is optional because applications may be for employment without knowing if a specific position slot is available. Each EMPLOYMENT APPLICATION may have an EMPLOYMENT APPLICATION STATUS TYPE associated with it such as “received,” “reviewed,” “filed,” “rejected,” “notified candidate of non-interest,” and so on. Each EMPLOYMENT APPLICATION may also be from an EMPLOYMENT APPLICATION SOURCE TYPE such as “newspaper,” “personal referral,” “Internet,” and so on. Each application may be from one and only one PERSON who represents the candidate. The application may also be referred from a PERSON that is the referrer of the candidate.
Figure 9.10 Employment application.
Employee Skills and Qualifications
An important aspect of human resources is keeping track of the skills, qualifications, and training levels for people and organizations. This information is needed to facilitate parties being placed in the most suitable positions based on their skills and qualifications.
Figure 9.11 provides a model that helps to maintain the skills, qualifications, and training of not only employees, but of all people and organizations for whom this is useful. Each PARTY may have one or more PARTY QUALIFICATIONs or PARTY SKILLs because it may be useful to track these for both a person and/or an organization in assessing to whom to assign responsibilities. In order to maintain the level of training that people have, each PERSON may have one or more PERSON TRAINING instances, indicating which training programs he or she has attended. The RESUME entity records one or more textual descriptions regarding the background and qualifications of each PARTY.
Figure 9.11 Employee skills and qualifications.
One may think that qualifications, skills, and resumes should be related to PERSON and not PARTY. Organizations may also have qualifications about which they are judged, skills that they specialize in, and resumes that describe their talents.
Part of the human resource function is tracking the effectiveness of employees over time. Employees are generally evaluated periodically, and an audit trail of comments or notes may be recorded about employees.
Figure 9.12a provides a data model that maintains information regarding performance reviews and notes that can be used to provide an audit trail for employees. Each EMPLOYEE may be the receiver of one or more PERFORMANCE REVIEWs. The receiver is the person for whom the performance review is written. Each EMPLOYEE PERFORMANCE REVIEW is from one and only one MANAGER responsible for the review. If the enterprise has a need to track more than one manager who may give a specific review, then this relationship may need to be many-to-many. Each EMPLOYEE PERFORMANCE REVIEW may be composed of PERFORMANCE REVIEW ITEMs that represent the various questions that may be asked on a performance review. An example of this is “Rate employee's promptness” or “Describe employee's teamwork style.” Each PERFORMANCE REVIEW ITEM represents the specific item as it relates to that review, along with a possible RATING, as illustrated by relationship to the RATING TYPE, for that item and a comment (some items may not have a rating and only a comment if the item asks only for a description).
Figure 9.12a Employee performance.
Each EMPLOYEE may be the receiver of one or more PERFORMANCE NOTEs, which is another mechanism for documenting an employee's performance. An example is that there may be NOTE on particular communication dates on January 20, 2001, with a comment of “Hal Smith was insubordinate and refused to listen to the direction of his manager.”
The information maintained in Figure 9.12a is mandatory for employees in most organizations. The model may also be expanded to include this information for other people such as contractors for whom performance reviews may also be given, if this is a need of the enterprise. Some enterprises may even want to track performance reviews for organizations that provide goods and/or services to them. In order for the model to be expanded, the relationships for PERFORMANCE NOTE and PERFORMANCE REVIEW would be to either PERSON or PARTY, depending on whether organizations also were going to receive performance reviews and performance notes.
Raises, bonuses, promotions, and demotions are important pieces of information that may arise from a PERFORMANCE REVIEW. Therefore, the PAY HISTORY, PAYCHECK, and POSITION entities are related to the review. Raises or cuts in salary are maintained within instances of PAY HISTORY and may be affected by a PERFORMANCE REVIEW. Bonuses would be maintained as a PAYCHECK and may be arising from a PERFORMANCE REVIEW. A POSITION may be influenced by a PERFORMANCE REVIEW, as the review may lead to a promotion or demotion if it is related to a certain POSITION.
Another alternative to the model in Figure 9.12a is shown in Figure 9.12b. This model maintains the PERFORMANCE NOTE as well as the PERFORMANCE REVIEW information as a subtype of COMMUNICATION EVENT PURPOSE with a corresponding COMMUNICATION EVENT describing the purpose for a PARTY RELATIONSHIP of EMPLOYMENT. This model also provides for the fact that a PERFORMANCE NOTE or PERFORMANCE REVIEW may be for any PARTY that is involved in the COMMUNICATION EVENT. For example, performance notes or reviews could be kept for contractors who would have a “contractor being reviewed” COMMUNICATION EVENT ROLE.
Figure 9.12b Employee performance-alternate model.
Should performance notes and reviews be considered types of communication events? The answer could be “yes,” as many of the same attributes and relationships are applicable. However the first model (Figure 9.12a) treats performance notes and performance reviews as separate entities, as they are very sensitive in nature and would most likely be maintained separately. The modeler needs to decide which model is most appropriate for the enterprise being modeled.
Unfortunately, or fortunately, depending on how you look at it, there is a need to track information about the termination of employees. The dates of termination, reason for termination, and type of termination, as well as possible aftereffects such as unemployment claims, are important pieces of information to capture.
Figure 9.13 captures information about employee terminations. As stated previously, the EMPLOYMENT entity is a subtype of PARTY RELATIONSHIP that establishes a relationship between an EMPLOYEE and an INTERNAL ORGANIZATION. When the EMPLOYMENT ends, the thru date of the PARTY RELATIONSHIP stores the date of termination. On termination, the PARTY RELATIONSHIP STATUS TYPE instance of “terminated” is now used to signify that the employment has ended. The TERMINATION TYPE stores what kind of termination happened, such as “resignation,” “firing,” or “retirement.” The TERMINATION REASON maintains the description explaining the circumstances and cause of the termination. Examples include “insubordination,” “took new job,” “non-performance,” “moved,” and so on.
Figure 9.13 Employee termination.
The UNEMPLOYMENT CLAIM maintains information about unemployment claims that may be related to terminations of employees. The claim date shows what date the unemployment claim was filed. The description provides information on the background of the claim, and the relationship to UNEMPLOYMENT CLAIM STATUS TYPE stores whether the claim was “filed,” “pending,” “accepted,” “rejected,” or any other important status. If a history of statuses is needed, then the model needs to include a many-to-many relationship between UNEMPLOYMENT CLAIM and UNEMPLOYMEN CLAIM STATUS TYPE. Other information may be associated with the UNEMPLOYMENT CLAIM, such as the various parties involved, notes about the claim, dollar figures involved in the claim, and so on, depending on the needs of the enterprise.
It is important to note that the employee has not really been terminated (unless the person passes away), even though that is the common means of expressing this fact. The employment is the thing that has really terminated. This is why the PARTY RELATIONSHIP STATUS TYPE is used to record the termination status as opposed to the PARTY STATUS, which is used to record information about the party irrespective of any relationship (this would be used to record if someone were “deceased”).
This chapter has discussed one of the more complex aspects of operating a business: the tracking and management of human resources information (see Figure 9.14). The models presented allow an enterprise to more efficiently track positions and assignments associated with its employees and contractors. In addition, the models contain elements for position classifications, reporting structures, determining pay rates, tracking the salary history of those people associated with the enterprise, benefits tracking, payroll information, employment applications, employee skills, employee performance, and employee termination.
Figure 9.14 Overall human resources model.
Please refer to Appendix A for a listing of entities and attributes. SQL scripts to build tables and columns derived from the logical models in this book can be found on the full-blown CD-ROM, which is licensed separately.