Planning Records, Control Tables, and TableSets

Planning is the first step in the application development process. As a system designer, you must consider how to store, retrieve, manipulate, and process data that is stored in the tables in your application database.

This chapter provides an overview of the planning process and discusses how to share tables.

Understanding the Planning Process

This section discusses:

• Table-based systems.

• Normalized relational databases.

• Record definition planning.

• Effective dates.

• Control tables.

• TableSets.

Table-Based Systems

PeopleTools-based applications are table-based systems. A database for a PeopleTools application contains three major sets of tables:

• System Catalog tables store physical attributes of tables and views, which your database management system uses to optimize performance.

• PeopleTools tables contain information that you define using PeopleTools.

• Application Data tables house the actual data that your users enter and access through PeopleSoft application pages.

This diagram shows PeopleSoft database tables and sample names:

Tables in a PeopleSoft database

Like a spreadsheet, each of these tables contains columns and rows. Columns define the structure of how the data is stored. Rows represent the actual data that is stored in the database.

Every time that you create a new definition using PeopleTools, the system inserts rows of data into various PeopleTools tables. The entries in these tables determine the online processing of the system and what happens during imports. PeopleSoft maintains the structure of these tables. You maintain data in the PeopleTools tables related to definitions that you create or adapt using PeopleTools. You can view the PeopleTools tables in the PPLTOOLS project using the PeopleSoft Application Designer.

To create the application data tables that store the rows of data that your users manipulate:

1. Create a record definition.

   In doing so you determine the structure of the table, the characteristics of the fields, and any online processing that you want to occur behind the scenes when a user enters data.

2. Apply the SQL Create option to build the SQL table in which your application data will reside based on a subset of parameters in your record definition.

   During this process, the system automatically gives the application data table the same name as your record definition, prefaced with PS_.

Normalized Relational Databases

To better understand the structure of your PeopleSoft system, you should be familiar with the concept of a normalized relational database. A normalized table adheres to standards that are designed to improve the productivity of the database user. Normalization makes the database more flexible, allowing data to be combined in many different ways.

The standards for a normalized database are called forms, such as first normal form, second normal form, and so on.

First Normal Form

The first normal form requires that a table contain no repeating groups of nonkey fields. In other words, when you set up a record definition, if you encounter a field that could have multiple occurrences, put that field in a separate record that is subordinate to the primary record definition (a child record). This setup allows unlimited occurrences of a repeating field rather than a specified number. Each row of data is uniquely identified by a primary key, which can be a single field or a group of fields that, when concatenated together, form a unique key.

For example, look at the record definition structure of the tables that we use to schedule exam times for different locations in our training database. Here are the necessary fields, in order of importance:

• LOCATION

• EXAM_DT

• EXAM_TIME

You know that you have multiple exam dates and times per location. You could set up record definitions to accommodate this data as follows:

Because multiple exam dates per location might exist, we added exam dates to the second record definition (child record) that is subordinate to the first (parent) record. Similarly, because one date could have multiple exam times, exam times are located in a third record definition that is subordinate to the second.

Second Normal Form

The second normal form dictates that every nonkey field in a table must be completely dependent on the primary key. If two fields make up the key to a table, every nonkey field must be dependent on both keys together. For example, if a table has the Employee ID and the Department ID fields as keys, you would not put the Department Name field in the table because this field is dependent only on the Department ID field and not on the Employee ID field.

Third Normal Form

The third normal form is a corollary to the second; it requires that a nonkey field not be dependent on another nonkey field. For example, if a table is keyed by the Employee ID field and Department ID is a nonkey field in the table, you would not put the Department Name field in the record because this field is dependent on a nonkey field (Department ID). You would find the Department Name field only in the table that is keyed by the Department ID field, not in any other table that contains Department ID.

With the third normal form, you store shared fields in tables of their own and reference them elsewhere. For example, you would not put the Department Name field in every record definition in which the Department ID field appears. Instead, you would create a prompt table of department IDs and department names. Similarly, you would create a prompt table of job codes and job titles instead of putting a job title in every employee’s record.

Note. When designing record definitions, you should adhere to the third normal form to increase flexibility and reduce data redundancy.

Record Definition Planning

Before you begin to create record definitions, you should know how you plan to use the record definition, the fields that it will contain, special edits that you would like to see performed on the record definition, or specific fields in the definition.

You define two layers of information:

• Record level

• Field level

At the record level, determine the ultimate purpose of the record definition and how it will be used in the system. Is it destined to define an underlying SQL table to hold data? Are you building a view to join or retrieve information from other tables? Do you need a temporary work record where you can store derived data?

You can audit record-level changes, as opposed to individual fields contained in the record definition, which is an efficient alternative if you plan to audit several fields. You can also establish a more sophisticated use of record definitions, such as sharing information in TableSets and multilanguage controls, at the record level.

At the field level, plan the details of what types of fields to add. Should they be character fields or number fields? Should automatic formatting be used? What are the keys to the data stored in the database? Which fields should you audit? Do you want to specify prompt tables so that users can select from lists of valid values that are stored elsewhere in the database?

In most cases, if you are creating a record definition for a SQL table, you do nt have to worry about record-level definitions for parameters and conditions. Unless you change how a record definition is used, the system automatically assumes that you are defining a record definition for an underlying SQL table.

Effective Dates

Effective dates enable you to keep historical, current, and future information in tables. You can use the information to review the past and plan for the future. Three types of effective dates are available:

The EFFDT (Effective Date) field has special properties related to the processing of effective dates on rows and should be used only when needed.

Unlike regular date fields, which you can use anywhere in the system, use the EFFDT field only in record definitions for which you want to maintain data history—future, current, and past—to store rows of data in sequence. Using EFFDT fields only for this circumstance enables you to store multiple occurrences of data based on when it goes into effect.

For effective-dated rows, you can have multiple occurrences of future and history but only one current row of data.

EFFDT is almost always a key and almost never a list item. Activate the Descending Key attribute so that the row with the most recent effective date appears first on pages. You might enter %DATE (current system date) as the default constant for this field.

Note. Alternatively, you can use %CLIENTDATE as the default constant for the date field. %CLIENTDATE adjusts the date as appropriate to the time zone of the browser.

To enable you to track an accurate history of your effective-dated information, the system invokes special logic when you access a record definition that contains EFFDT. The action that you select dictates whether you can access the row type and what you can do with each type of row:

Note. For records that do not contain EFFDT, all actions (Update/Display, Update/Display All, and Correction) operate the same way: they retrieve all existing rows for the specified keys.

When you run a page with effective-dated records and you insert a row, the system copies the contents of the previous row into the new row to save you keying time. In a large effective-dated table, you do not want to reenter all of the data when only a single field changes. Also, anytime you insert an effective-dated row using PeopleCode, the system copies the contents of the previous row.

See Also

%ClientDate

%Date

Control Tables

Control tables store information that controls the processing of an application. This type of processing might be consistent throughout an organization (in which case the entire organization shares the same control information), or it might be used only by portions of the organization for more limited sharing of data.

Sharing One Set of Common Values

The first type of sharing is to create one table that everyone shares; it stores common information that is valid for all users, such as a country table to store country codes or a department table to store department codes. Such control tables are ordinarily maintained centrally because the data is shared throughout the entire organization.

Sharing Common Values in Overlapping Plans

What do you do if the codes that are stored in a table are valid only for some users? Consider benefit plans, for example. Typically, you store information for benefits plans in a plan table. However, not all plans are valid for all employees; their validity might depend on whether the employee is full-time or part-time, union or nonunion. Some plans might overlap; some might be appropriate for all employees and others only for some. In a relational database, you do not want to define the same plan value—and associated data—more than once.

In this case, you can easily resolve the problem by using two tables. The first is the plan table, which stores the relevant data for each plan. The second table defines which plans are valid for various benefit programs or groups of plans. For example, one benefit program might be valid for nonunion employees, and another benefit program might contain the plans as negotiated with a union.

This table shows how you might set up the two tables to reflect the benefits offered:

These tables are ordinarily centrally maintained because the data is shared by various groups in the organization.

TableSets

When none of the information stored in control tables is valid for all users, but the structure of these common tables is the same, you can set up a way to share multiple sets of values. For example, a multicompany organization must store completely different sets of accounting codes for its various operating entities, and the data for these accounting codes is maintained in a set of relevant control tables. The actual data values differ, but the structure of the control tables remains the same. PeopleTools enables you to share sets of values in a control table through TableSets.

To better understand TableSets, consider an organization that has two retail stores with common accounting codes, two pharmaceutical firms with another set of accounting codes, and two shipping firms with yet another set of codes.

Maintaining multiple account codes for multiple companies

If each of these companies has completely different accounting codes, you can establish six different sets of account codes to be maintained by each company. If they all have exactly the same accounting codes, you can limit them to one set of values. However, the reality is usually somewhere in between. That is, there is one set of account codes for each type of business: retail, pharmaceutical, and shipping. Rather than having six different companies maintaining separate copies of this common data, you can reduce the number to three sets:

Sharing multiple account codes among companies

You can also handle exceptions. Suppose that Retail Company 2, a recently acquired company, has its own unique set of account codes. A separate set of values should be maintained for this company as an exception to the retail rule:

Sharing account codes among companies with exceptions

When you share tables in PeopleTools applications, you add the setID field as an additional key or unique identifier to the table that you want to share. This key identifies the sets of information in the table that are shared by multiple companies or business units under your corporate umbrella. You then specify a set control field, which identifies which fields map between the original key and the TableSets. You can specify any field that logically identifies the TableSet. In this example, you might assign the Company field as the set control:

Linking set controls and TableSets

Sharing Groups of Record Definitions

While this example illustrates how you might share data values for a single table—Account Codes—you typically share data that is stored in many tables that are based on the same TableSets. To minimize the overhead of defining TableSets, you can define record groups that share table data in a similar manner. For example, rather than using the TableSets that you establish for accounting codes solely for the Accounting Code table, you can group all accounting related tables into one record group.

TableSets and PeopleSoft Applications

Some PeopleSoft applications already take full advantage of TableSets and table sharing. Throughout the PeopleSoft Financials and HCM product lines, TableSets are used extensively, in most cases triggered by business unit.

Sharing Tables

This section provides an overview of table sharing and discusses how to:

• Add the SetID field to record definitions.

• Define set control fields as the fields controlling the assignment of TableSets.

• Modify set control fields.

• Create setIDs.

• Define record groups to identify the tables and subordinate (child) tables that are affected.

• Define TableSet controls.

• Share trees.

Understanding Table Sharing

To share tables, you must modify the record definitions for both the table that you want to share and the one that contains the set control field. Using the online PeopleTools Utilities for TableSets, you define the terms or controls for sharing.

For example, the PeopleTools database holds data for three companies: one U.S.-based parent company (PST) and two sister companies (CCB in the U.S. and VNB in Canada). In this organization, all of the U.S.-based companies share one set of accounting codes, and all of the Canadian-based VNB companies share another:

These two groups of companies must maintain parallel sets of accounting codes and, in some cases, use the same code, uniquely identified for U.S. or Canadian companies.

You must set up the Account Code table so that users who access that page or prompt for valid values see only the values for their respective companies, depending on whether they are located in the U.S. or Canada.

To do this:

1. Modify the Account Code table, which will be shared, and the Company table, the values of which (CCB, PST, VNB) control the TableSets that are used.

2. Create two TableSet IDs:

   • USA: For U.S. companies.

   • CAN: For Canadian companies.

TableSets and set control fields

Assigning Set Control Fields

If you plan to use the Table Sharing feature to add an additional high-level key to identify common sets of values and handle exception values, you enter a set control field. The set control field determines which set of values appears, based on how you define table sharing.

Table sharing is usually applied in multicompany environments, in which you might want to share certain tables in some operating entities while allowing others to retain control over the contents of their own. Use the feature to maintain a single table in which you can store multiple sets of values to be used by different entities in your organization.

When you create a record definition that will be shared, you add the field setID as a high-level key. The setID value is controlled by the key that you select in the Set Control Field drop-down list box in the Record Properties dialog box. These setIDs identify groups of tables to be shared and enable each business entity to identify whether it wants to use the corporatewide table or maintain its own. Enter the name of the key as your set control field for each record definition that will be shared.

For example, if you have several companies in your organization, many of which share the same set of account codes, add a SetID field to the Account table record definition. In this definition, enter Company as the set control field. The set control enables different companies to access the account values stored in the same Account table. As each company defines its set controls, it has the option to use the shared Account table or maintain control of its own.

Adding the SetID Field to Record Definitions

Add the setID field to the record definition for table sharing purposes.

To add a setID:

1. Open the record definition for the table that you want to share.

2. Select New, Field, Character.

3. Enter the special field that PeopleTools provides for table sharing:

   SETID

   SetID is a 5-character field. Add it to the top of your record definition list, and define it as a required key, search and list item, with a prompt table edit against the PeopleTools SETID_TBL.

Defining Set Control Fields

After you add the setID field to your record, identify the set control field in Record Properties.

To define the set control field:

1. Select Edit, Definition Properties from the menu.

2. Select the Use tab.

3. Select the field that identifies the appropriate setID in the Set Control Field drop-down box.

   See Understanding Table Sharing.

4. Click OK to save your changes.

5. Use the SQL Alter process to alter your underlying SQL table.

6. When the system prompts you for a default setID, enter the most common setID for your company.

Modifying Set Control Fields

If you are sharing tables or using a set control field that is not already defined as such in your PeopleSoft application, you must make some modifications to the record definition for the table in which you store values for your specified set control field. For example, all PST company codes are stored in the COMPANY_TBL.

Here, you must add PeopleCode to the set control field so that each time you add a new value, the system populates the set controls in PeopleTools utilities, where you assign the appropriate TableSets.

This program assigns a default setID with the same value as the Set Control Field value, for each new COMPANY row that you add to the COMPANY_TBL. For example, when you add a row for CCB in the Company table, it creates a default setID of CCB. This way, each value that you add is assigned a unique TableSet until you assign shared TableSets in the Utilities window.

You can easily clone and modify this program to change the ADD_SETID and ADD_TABLESET_CNTRL statements to reflect your table sharing objectives. Alternatively, if you are working with existing TableSet functionality, you can point to a model setID that is delivered with your PeopleSoft application.

See Also

Altering Tables

Creating SetIDs

After you prepare for sharing your table, you define the setIDs that logically group information. When you define setIDs, you create groups that might naturally share more than one table. For example, although CCB and VNB are planning to share only the ACCT_CD_TBL now, the setIDs that you set up might define logical divisions in the organization that serve as the basis for sharing all accounting-related tables. Describe your sets to give them as broad an application as is practical in your organization.

If possible, use descriptions that denote the shared table and set control field. For example, for CCB and VNB, we added two TableSet IDs, identifying them as related to accounting codes for companies:

You create SetIDs on the TableSetID page in PeopleTools, Utilities, Administration, TableSet IDs.

See Also

TableSet IDs

Defining Record Groups

Record groups are a set of logically and functionally related control tables and views. They help enable TableSet sharing, which eliminates redundant data entry and ensure that it is applied consistently across all related tables and views.

In the record group table, you group the record definitions for the tables that you want to share and any dependent record definitions. If you add a table to a PeopleSoft application, an appropriate record group might already be defined. However, if you add new business functions, you might need to add a new record group for the tables that you define. You access the record groups table in PeopleSoft Pure Internet Architecture on the Record Group page.

See Also

Record Group

Defining TableSet Controls

As you add values for your set control, the system automatically populates the TableSet Controls 1 table with default values. You define TableSet controls in PeopleTools, Utilities, Administration, TableSet Control using the browser. Here you can enter the set control value for which you want to assign a TableSet.

For example, we use Company as our set control field, and PST, CCB, and VNB are values that we want to add to the Company table. The system automatically assumes that each new set control value maintains its own set of tables and does not enable table sharing. So, the default values are the same as the set control value. In this case, when we added the value CCB to the Company table, the system populated the TableSet controls with a default setID of CCB.

Because CCB is a U.S.-based company that should share the same U.S. accounting codes, we must change the defaults to USA.

See Also

TableSet Control

Sharing Trees

To share trees as well as tables and views, complete TableSet Controls 2 in the same way that you did TableSet Controls 1. For example, if PST and CCB want to share one department tree for organizational security and VNB another, you assign the appropriate setID for each value.

Defining TableSet Controls for Trees

To define TableSet controls for trees:

1. Select PeopleTools, Utilities, Administration, TableSet Control in PeopleSoft Pure Internet Architecture.

2. Enter the name of the set control value for which you want to assign a TableSet on the search page.

   For example, we use Company as our set control field, and PST, CCB, and VNB are values that we want to add to the Company table.

   Because you already assigned a default setID in TableSet Controls 1, the system displays the default setID that you assigned to this field value. If you create another TableSet for sharing trees, you can change this value.

3. Specify the tree name.

   Click the prompt button next to the Tree Name field for a list of only the tree definitions with the same set control field (which has PostSave PeopleCode that recognizes it as a TableSet control field).

   This prompt list comes from a SQL view of tree definitions with those set control fields that have not already been associated with a tree group.

4. In each tree row, specify the appropriate setID.

5. Save your changes.

Viewing All Record Definitions Associated With a Set Control

You can view all record definitions that are associated with a set control on the Record Group tab of the TableSet Controls page.

Example of Set Controls

After you set up and define all of your set controls, you can see the results by looking at pages on which you reference codes that are stored in a shared table. For example, on Administer Workforce, Use, Job Data 1 and 2 pages, Account Code is an optional field that is edited against the ACCT_CODE_TBL. In this component, the system identifies the company based on the department to which an employee is assigned. For example, Simon Schumacher is in department 10100, which is associated with the company CCB.

Select the Job Data 2 tab and enter 8001 or Simon Schumacher in the search record dialog box to retrieve a CCB row, for which the company is defined in the department table. Here, you can see that Simon works in department 00001, which identifies him as a CCB employee. If you click the prompt button next to the Account Code field, the system displays only those rows in the Account Code Table that are associated with USA, the setID for CCB.

Alternatively, if you access the job row for Joan Avery, an employee of VNB, and click the prompt button for the same Job Data 2 Account Code field, the system retrieves only values that are associated with CAN, the setID for VNB.