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:

Benefit Program Table		Benefit Plan Table
Key	Valid Values	Key	Description	Field	Field
Nonunion Program	Plan 1 Plan 2	Plan 1 Plan 2	Health Life	... ...	... ...
Union Program	Plan 2 Plan 3 Plan 4	Plan 3 Plan 4	Savings Health-Union	... ...	... ...

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