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 non-key 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:

Record Definition	Fields	Key?
LOCATION	LOCATION	Yes
EXAM	LOCATION EXAM_DT	Yes Yes
EXAM_TIME	LOCATION EXAM_DT EXAM_TIME	Yes Yes Yes

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 non-key field in a table must be completely dependent on the primary key. If two fields make up the key to a table, every non-key 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 non-key field not be dependent on another non-key field. For example, if a table is keyed by the Employee ID field and Department ID is a non-key field in the table, you would not put the Department Name field in the record because this field is dependent on a non-key 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.