A key criterion for selecting an HRIS is how flexible the application is. By flexible, I mean how readily the application supports your own specific business needs, particularly if these needs are complex or liable to change over time.

From my experience, the most important factor that affects how flexible an application will be is its database design. The goal of a good database design is to closely model the business environment the application operates in. If a database design successfully models this business environment, the more likely it is that the application will be flexible enough to handle complex requirements, without jumping through hoops.

As a result, a simple step I would recommend be always done when evaluating an HRIS is a review of its database design. This design is often documented in what is termed an Entity Relationship Diagram. Vendors, subject perhaps to the signing of a non-disclosure agreement, should be willing to provide this as part of a due diligence process.

At first glance, such a document might appear daunting. You shouldn’t be surprised, if you are not familiar with these, that it will take a bit of time till you are comfortable reading them. However, don’t be put off. The aim of this article is to help payroll people, who might not be familiar with the principles of database design, understand a few key points in what they are seeing.

A simple entity relationship diagram might look like the following:

In these drawings, everything in a box is a database table. The primary key fields for the table are listed above the line and the ordinary fields below. By primary key, I mean the fields that limit what duplicate values are allowed. There might be several such keys defined, for example, on first and last name, to ensure employee names are unique. The line between each table shows how they are related, with the arrow symbol on a line meaning a one-to-many relationship, for example, many employees to one employer.

In the above, a person can only be defined once, by a single CRA Business Number. If, for example, a single person has several distinct periods of employment with the same employer, the same person has to be entered as separate records under a different employee id for each separate period of employment. Effectively, such a design can’t keep the history of a single person’s separate periods of employment with the same employer. This, for example, was a common restriction of early payroll software design.

The following design is the first step-up in addressing these limitations.

With this design, it is now possible to have more than one period of employment, by job or position, for a single employer, while keeping the history of each such employment; and without having to change a person’s employee id, each time. This design also means that a person can have more than one job or position at a time, within the employer.

However, this design is still limited, since a person can only hold a single position once, by employer. For example, what happens if the person goes on a parental leave and then returns to work. This properly speaking requires three separate records in the middle table above: one, to reflect the active employment during the leave, another for the leave itself and a third record for the return to work. The above design can’t handle this, without changing the person’s position.

However, look what happens if we change the keys slightly:

Moving status and hire date into the unique keys on the person’s position or job now means that the same person id can be used to track a series of status changes over time. The same person and position can be used to show the history required of a person who goes on a leave and returns to work.

These are fairly simple examples, yet they show, by closely examining a vendor’s database design, how you can quickly get a sense of to what degree an application might meet your business needs. It does take a little time and it does take some getting used to, but the effort will more than repay itself by helping to select the best HRIS system for your specific needs.

Alan McEwen is a payroll consultant and freelance writer with over 20 years’ experience in all aspects of the industry, particularly in payroll software development. He can be reached at armcewen@cogeco.ca, (905) 401-4052 or visit www.alanrmcewen.com for more information. This article first appeared on the Canadian HR Reporter and Canadian Payroll Reporter websites on January 29, 2013.