The Umana data base

Your Umana data base contains information about your company, and your employees.

Conceptually it's simple, with the two parts — company data, and employee data. We'll look at them in a moment.

Your data base is exclusive to you. It has your data only. It is never used by any other Umana client, even if we host it for you. It is not shared.

Data base software (DBMS)

We recommend using SQL-SERVER from Microsoft to store Umana data. It is fast and reliable.

• Microsoft SQL Server is a Data Base Management System (DBMS). It stores and secures the data, handles caching, ensures consistency, and manages back-ups and logging.
• For this kind of data SQL-Server is the most widely used DBMS, and it not too expensive.

Data Environments

You might actually have multiple Umana data bases : a PROD data base, a TEST data base, and maybe a TRAINING data base.

• They can help you do simulations, experiment with new features or approaches, test new customization, etc.
• They are typically refreshed from PROD when needed.

More precisely, an environment is a combination of a version of Umana programs and a Umana data base. Your TEST environment might also have a newer version of Umana programs for testing.

Company data

Company data describes your company. It tells Umana how to categories employees and apply your rules. For example:

• Your pay codes
• Your status codes
• Your departments and G/L codes
• Settings, alerts, watches, etc.
• Your benefits and time banks offered, and their rules
• Your remuneration and seniority rules
• Your policies and your collective agreements is coded up as rules.

Some company tables have full history and effective dates. Most don't (but you can still keep old codes and mark them obsolete).

Employee data

Employee data is about your employees.

Every employee has a unique employee number. In Umana we call it the PERSID field. An employee number can be up to 6 digits.

Every employee record has a PERSID field. Company tables don't.

An employee's number doesn't change : Even if he is terminated and rehired. Even when he changes province or CRA number.

• The employment status (JSTAT) field indicates whether the employee is active, retired, terminated, etc.

The PERS record

Think of the PERS record as a master record for the employee.

The PERS table has exactly one record for each employee, past or present.

• It only keeps current information : the person's current employment status, current department, current job, etc.

Other employee records

Other employee tables keep past (history) and future (planning) information about the employee. In other words, these records carry an effective date — the date the record applies to or as of.

Here are a few of them. (Click the table name for more detail.)

Employee history — JOBHIST

• Tracks changes to status, job, salary, department, etc.

Benefits and Time banks: BENEFIT, TIMSUM

• Tracks benefit and time bank (vacation, sick days, ...) enrollments

Time worked and absences (gross pay) : TIMEDT

• Detail of what each employee did each day, maybe even hour-by-hour. Includes job performed, where, premiums, hours, rate, etc.
• Can come from a timesheet (on phone or desktop), a punch clock, another system, etc.

Planned work and absences : SCHED, Absence Planner

• Repeating schedules, exceptions, and vacations or other planned absences.
• Possibly fed from an external scheduling system.

Payroll (net pay) : PAYNET

• What each employee earned each pay period, and deductions taken.
• Earnings come from the gross pay (TIMEDT)
• Exports EFT (direct deposit) file to bank, and transactions to your GL (General Ledger). Creates T4s etc each spring.

Miscellaneous other events : EVENTS

• You can create any other kind of events you need : Evaluations, Equipment lent, Disciplinary actions, Certification, ... Essentially any other event in the life of an employee can be tracked.

Documents : DOCCAT and BLOB

• You can store documents and attach them to an employee record in the data base. This might be a medical note for an absence, an employment contract, an evaluation, etc.

Next topic

So we've looked at the data base. Let's look at the Umana programs