Database terms

Before using the features of Sage HRMS, it is helpful to review some basic database concepts and terms.

Database File

A database file is a series of records. If you are familiar with electronic spreadsheet software, think of a record as a row in the spreadsheet. Each row contains data about one specific employee in the database. In an employee database file, each record contains data relating to one employee.

A simple employee database file looks like the following table:

Sample database table

 
L_NAME F_NAME SSN ID_NUMBER

Birkin 

Stanley 

129-61-7090 

101 

McTimm 

Jake 

222-12-0148 

102 

Thornton 

John 

322-73-8961 

103 

Signor 

Gina 

444-78-2334 

104 

Kokai 

Thomas 

552-45-6456 

105 

 

There are as many records in the database file as there are employees. The database has a file name and file extension. The master database file in the system, HRPERSNL, contains basic information about an employee's identification, Social Security number and address.

Each record in the database has one or more fields. Think of a field as one column in the spreadsheet. Each field contains one item of data relating to the record.

A field in a record is categorized by its field type. The databases in Sage HRMS have fields with the following field types:

  • Numeric - contains only numbers (0 through 9)
  • Character - contains letters of the alphabet, numbers, or special characters such as commas or dashes
  • Date
  • Memo
  • Logical

Every field in the record has a name for identification purposes. Usually the field name is an abbreviation describing the data in the field. The field name for the employee last name might be LNAME, L_NAME or LAST_NAME. For example, in the Sage HRMS system, the last name field name is P_LNAME.

Another characteristic of a field is its length. A field for a Social Security number might be nine characters in length, or 11 if the hyphens in the number are part of the data. The field length for the employee last name is 25 characters, the length of the longest last name you are most likely to encounter. The length of a numeric field establishes the largest number that can be stored in that field and includes both the total length of the field and the number of positions to the right of the decimal point.

Expressions and functions

Expressions and functions are tools to manipulate data. An expression is similar to a mathematical formula; it is a statement of comparison between numbers or other data. Certain tasks within Sage HRMS require you to create an expression to help identify the records you want included in a task or process.

For instance, when using the Mass Update process in Sage HRMS, you might want to search for all employees whose original hire date is before January 1, 2022. The expression builder, a feature which is a part of the process itself, helps you create the following expression: P_ORIGHIRE < {01/01/2022}

The expression consists of three parts: a field, a relationship, and a value. In this case the field is P_ORIGHIRE, the relationship is "less than" and the value is {01/01/2022}.

A function is a quick way to change how data is represented in the system. For example, the value of the field L_NAME might be "Nelson." But if you want this name to appear with all capital letters, the function "UPPER(L_NAME)" does this for you. The value corresponding to UPPER(L_NAME) is "NELSON."

Database structure

The database structure is a list identifying the name, type and length of each field in the database. In a complex database, the structure might also include a short description for each field to explain the purpose and use of the field. The database structure for the sample employee database used here is illustrated by the following table.

Sample database structure

 
Field name Type Length

L_NAME

C

25

F_NAME

C

14

SSN

C

11

ID_NUMBER

N

3,0

 

The L_NAME and F_NAME fields are both character fields. The SSN field is a character field rather than a numeric field since it also includes the dashes in the number. The ID_NUMBER field is three positions long, meaning the largest number we can store in the field is 999. The zero means the ID_NUMBER does not contain any numbers to the right of the decimal point.

Data dictionary

A data dictionary is a documentation tool which lists all the database files and their structure. You will find it useful if you use expressions and functions or if you use SAP Crystal Reports.

To obtain the most updated version of the data dictionary, you can run the Data Dictionary reports.

  1. From the menu, select Reports > System > Data Dictionary Reports. The Data Dictionary Reports dialog box opens.
  2. To print a list of database files, select Files List. To print a list of all the fields within each database file, select Fields List. To print a list of the Index Keys, select Index Keys List.
  3. Select the appropriate check boxes for the products you want to appear on the report.
  4. Click Preview to preview the report. When you are finished previewing, click the Close button to close the preview window.
  5. Click Print to print the report.
  6. Click the Close button to close the Data Dictionary Reports dialog box.