PS Technical: PeopleSoft Tables

Table-Based Systems

• 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.

Normalized Relational Databases

• First Normal Form
  The first normal form requires that a table contain no repeating groups of nonkey fields. In other words, when you’re setting 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 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.
• 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.
• 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. With the third normal form, you store shared fields in tables of their own and reference them elsewhere.
• Note: When designing record definitions, adherence to the third normal form is recommended to increase flexibility and reduce data redundancy.

Record Definition Planning

• Record 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—an efficient alternative if you plan to audit several fields. More sophisticated use of record definitions, such as sharing information in TableSets and multilanguage controls, are also established at the record level.
• Field 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 don’t 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. There are three types of effective dates:

• Future
  Data rows that have effective dates that are after the system date, which is usually today’s date.
• Current
  Data row with the most recent effective date that is closest to today’s (system) date, but not a future date. Only one row is the current row.
• History
  Data rows that have effective dates before the current data row.
• Note 1: When you’re running a page with effective-dated records and you insert a row, the system copies the contents of the prior row into the new row to save you keying time. Also, anytime you insert an effective-dated row using PeopleCode, the same copying of the prior row’s contents takes place.
• Note 2: If EFFSEQ is paired with EFFDT, it enables you to enter more than one row with the same effective date. You assign a unique sequence number to each row that has the same effective date. Do not make EFFSEQ a required field—unrequired allows the first EFFSEQ to be zero. Select Display Zero in the page definition to have zeros appear on the page.

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 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? In this case, you can easily resolve the problem by using two tables.These tables are ordinarily centrally maintained, because the data is being 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. 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.

• 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.

• To minimize the overhead of defining TableSets, you can define record groups that share table data in a similar manner.

Sharing Tables

• Add the SetID field to the record definition.
• Define a set control field as the field controlling the assignment of TableSets.
• Modify the set control field.
• Create setIDs.
• Define record groups to identify the tables and subordinate (child) tables that are affected.
• Define TableSet controls.
• Share trees.
• Unable to create a sample :(