## Control Tables - Examples and Power

To Basic Theory

Control Tables are tables that control the program flow or play a major part in program control. Control Tables often have expressions or function references stored in them. Control Tables reduce the need for programming similar structures over and over again. The two-dimensional nature of tables makes them easier to view and update than the one-dimensional nature of program code. In some cases, non-programmers can be assigned to maintain the control tables.

The Data Dictionary is one fairly common example of a control table because it "controls" field behavior. For a simpler example, suppose you have a system that calculates sales commissions. Supplied to the system is a list that looks something like this:

```0001, "xrate() * tricalc() - 23.5"
0002, "tricalc()"
0003, "demote() * 1.2"
0004, "early() - tricalc() + 3"
```
The first field is the salesperson ID, the second is an expression used to calculate the corresponding salesperson's commission.

Such a list is built so that a paycheck expert can supply the commission equations instead of having the programmer recompiling for every change. (The programmer still has to build special functions like our Tricalc() example, but this would be infrequent. Also note that the format of this example table is comma-delimited, but that is not necessarily the same format that the paycheck expert has to use. It may come from a spreadsheet, for instance.)

The functions are implemented in the program code and use fields from the corresponding employee record (keyed on salesperson_ID). The program gets this table and evaluates (executes) the expressions for each salesperson in the list. This would use something similar to the Perl Eval() function or FoxPro's Evaluate() function.

## Theory

I was once trying to plan the interface to a sports memorabilia on-line store. My category plan looked somewhat like this:

```   BASEBALL
Autographs - 40
Cards - 80
Memorabilia - 20
Other - 8
Autographs - 20
Cards - 35
Memorabilia -15
Other -4
FOOTBALL
Autographs - 8
Cards - 15
Memorabilia - 10
Other - 2
OTHER SPORTS
Autographs - 10
Cards - 8
Memorabilia - 15
Other - 5
```
The numbers represent the number of items in that category.

Then I was thinking that some people may just want to deal with say cards or autographs, regardless of the sport. This plan then came to mind:

```   AUTOGRAPHS
Baseball - 40
Football - 8
Other Sports - 10
CARDS
Baseball - 80
Football - 15
Other Sports - 8
MEMORABILIA
Baseball - 20
Football - 10
Other Sports - 15
OTHER
Baseball - 8
Football    - 2
Other Sports - 5
```
I came to realized that there was no perfect answer. Grouping by sport sacrificed having media types (such as cards) together. And, grouping by media sacrificed having a sports-related grouping. A web search came up with both types of organizations used by other organizations.

It then dawned on me to try something like this:

Sport Autographs Cards Memorabilia Other
Baseball 40 80 20 8
Football 8 15 10 2
Other Sports 10 8 15 5

This way both perspectives are given. One no longer has to choose between one orientation or another.

A big issue in programming is how to group similar items. (This is sometimes called proximity.) In OOP, items are usually grouped by their "subclass". In traditional programming, items are grouped by operation (function). Control Tables treat each of the groupings as a dimension, and thus allow grouping by BOTH subclass and operation at the SAME TIME because tables are two-demensional. Here is the basic pattern:

Typical OOP (Object Oriented Programming) Structure:

```  SubClass A
operation 1 (x)     // Note: "property" can be substituted
operation 2 (x)     //   for "operation". There also may
operation 3 (x)     //   be a base class, which is not
SubClass B              //   shown here.
operation 1 (x)
operation 2 (x)
operation 3 (x)
SubClass C
operation 1 (x)
operation 2 (x)
operation 3 (x)
```
```  Operation 1           // alias: "function"
SubClass A (x)
SubClass B (x)      // alias: "case" or "switch"
SubClass C (x)
Operation 2
SubClass A (x)
SubClass B (x)
SubClass C (x)
Operation 3
SubClass A (x)
SubClass B (x)
SubClass C (x)
```
Control Table Structure:

 SubClass Op. 1 Op. 2 Op. 3 A (x) (x) (x) B (x) (x) (x) C (x) (x) (x)

The first example (typical OOP) can be said to be in SOMPI grouping, where SOMPI stands for Subclass Outer, Methods/Properties Inner. The second example (traditional programming) can be said to be in SIMPO grouping (Subclass Inner, Methods/Properties Outer). SIMPO is simply an inversion of SOMPI. Some OOP proponents say that OOP gets rid of type-picking switch/case (SIMPO) statements, but it is really only inverting them in a good many cases.

This issue of grouping is really part of a larger abstraction issue that I refer to as the aspect issue. There are often multiple legitimate candidates for grouping and relationship mapping in general. OOP philosophy often artificially elevates the status of one aspect (relationship) at the expense of others.
Now lets look at an example of employee payroll calculations. The OOP approach would probably resemble:
```SubClass employee_type1 {
method calc_pay {
pay = blah * blah - blah etc...
}
method print {
blah blah
}
method calc_tax {
blah blah
}
}
SubClass employee_type2 {
method calc_pay {
blah blah = blah * etc...
}
method print {
blah blah
}
method calc_tax {
blah blah
}
}
SubClass employee_type3 etc...
```
(Note that there would usually be an "employee" super-class, but we omitted it for brevity.)
Now, the procedural approach may look like:
```Procedure Calc_pay(record r) {
case r.pay_type = 1
r.pay = x * stuff(y);
case r.pay_type = 2
r.pay = blah blah...
case r.pay_type = 3
r.pay = x * y - z
otherwise
r.pay = hours * rate;
}
Procedure Print_pay(record r) {
case r.pay_type = 1
line = blah blah
case r.pay_type = 2
line = blah blah...
etc...
}
Procedure Calc_tax(record r) {
case blah blah blah
}
```
It is often said that the OOP method is "better encapsulated" because the addition of or change to an employee type only involves changes to one class, whereas we would have to add or alter "case" statements in all three routines in the procedural code. (Case statements are also called "switch" statements in some languages.)

This is true of "object oriented changes" (OOC). OOC's are certainly a little tougher to do in procedural code. However, many changes are not OOC, but rather procedure-oriented changes (POC). Many OOP books fail to point this out, only giving OOC examples.

For example, the government may pass a law that changes the way many payroll calculations are done. In the procedural example, only the Calc_pay routine needs to be changed, yet in the OOP example, all employee type classes have to be visited separately.

Thus, OOC's are easier to do in OOP and POC's are easier to do in procedural programming. Having all related procedural code in one place has encapsulation advantages over OOP under many circumstances. (Our shapes example gives a specific demonstration.)

In addition, the "otherwise" clause in our case statement provides the same kind of inheritance feature that OOP would provide if a procedure was inherited from a parent class. In the procedural code, you can chose to define a specific calculation, or let the default take care of it. Similarly, in the OOP code, you can "override" the parent procedure, or simply inherit it (by not coding it). (Subroutines can also be used to get the same effect as 3 or more levels of OO inheritance.) See the Inheritance Compared document for more examples.

OOP fans like to brag about how adding a new subclass is easier in OOP because you only have to create a new subclass rather than visit each related procedure to add a new case/switch element.

SOMPI proximity (OOP) does tend to favor adding NEW subclasses in some situations. However, I find that I prefer to see how RELATED subclasses implemented a given method. I usually end up with a set of routines or variables that help out for a majority or many of the related methods. Thus, one rarely just builds a new subclass without looking at EXISTING ones and comparing them, or even sharing elements. Coding an isolated subclass in pure isolation simply rarely happens in the real world. Thus, the "single visit point" benefit of SOMPI additions is mostly on paper. (If the additions become too frequent, then I switch to Control Tables.)

How does this all relate to control tables? Well, control tables can provide the best of both worlds because they are two-dimensional, while code is one-dimensional. Rows usually correspond to related classes, and columns usually correspond to related properties or methods. Our payroll example could look like this in control table form:

Example Control Table for Payroll
Empl-
type
Descript.Calc_pay Print_funcTax_calcaflag
Acommision\$base + commis1(\$sales) + xfmt2(\$hours,\$sales,
"###.#, ###.##")
blah * zYes
BSalaryx * y - z(x)fmt2(\$hours,\$OT,
"###.# ##.#")
blah * blahYes
Cblah blah\$hours * blah(foo)fmt3(blah)blah - 12No
Dblah blah**x + blah2(foo)Yes

The code to evaluate the relevant portion of the table is rather generic. The Calc_pay routine for a given employee record might look something like this:

```Procedure Calc_pay( record r ) {
with r do {   //make r default record (\$)
// find control-table entry
locate for ct.empl_type = \$empl_type  //find table entry
if found() {
if (ct.calc_pay == "*") {  //default?
\$pay = default_pay(r)
} else {
\$pay = evaluate(ct.calc_pay) // expression in ct
// (or sometimes just process a simple code)
}
} else {
// handle missing type
}
} // end-with
} // end procedure
```
This is not the only way to code it, but it shows that the code is usually not dependent on specifics of the classes (employee types). It may even be possible to have this lookup be built into the language. Note that the control table stores function calls as strings, not as pointers. (Although pointers may have to do if your language does not support internal string evaluation.)

An SQL-minded approach could look something like this under some conditions:

```  Select ct.*, data.* From cntrlTBL ct, data
Where ct.type = data.type
```
(Note: The employee example is probably not very real-world in terms of subtypes for reasons that are described elsewhere. It was only chosen for introductory illustration purposes.)

Our Bank example provided examples of ways to simplify and "factor" the coding of control table handling to avoid repeating lookup structures throughout the code. Although the Bank example is for boolean operations, it's concepts can often be extended to other types of cell contents.

In our example, if you want to add or change an employee type, you simply add or change a row in the control table. If you want to add or change an operation category, you simply concentrate on a column. Many table viewers allow you to "lock" a column to avoid accidental changes to other columns. The same applies to rows. In addition, you can sort, filter, or size the way rows and columns are displayed. Code viewers cannot provide such virtual view reorganization. More examples of table viewer features will be given later.

When this information is in code, you are FORCED to either group related code by object (OOP classes) or by function (procedural). Control Tables avoid this singular choice by adding another dimension. (Note that you can also group parts of OOP subclasses by function using more layers of organizational references, but you are still limited to one or the other.)

### Inheritance and Encapsulation for Control Tables

Control Tables easily support one level of inheritance by allowing defaults for cells. Asterisks or blanks in cells indicate that a default method or property is to be used. This is similar to defining methods in a base class in OOP. More complex inheritance can be implemented by "class sets". A list of codes or keywords can be assigned to each row. You then assign methods that are activated for only the specified set or groups. This is similar to the "field groups" in our Data Dictionary example, where set commands can be used to select which fields appear on a given screen, report, or export file.

This also shows that one does not necessarily have to put entire functions or expressions in cells when a simple code will suffice. The decision to use expressions or codes depends on the circumstances. Codes are usually used if there is a lot of repetition in expressions or if multi-code sets are planned. (Note that codes can be meaningful names, not just single letters. Also, the idea of putting the entire subroutine in code has yet to be explored, but perhaps could be a boon.)

This set-oriented approach is more flexible than the hierarchy approach used by OOP because one is not forced into a tree structure in order to inherit behavior. For example, it would be hard to choose groups of fields based only on hierarchical criteria. Hierarchies are nice if you can fit them, but they are not general-purpose structures.

To prevent a method or property from being attempted on an inappropriate class, "N/A" or perhaps an error handling method name could be put in the relevant cell. Although this would be a run-time check, there is no reason why a Lint-like utility (pre-checker) could not be built to point out such potential problems before run-time.

### The Benefits of Control Tables over OOP

The goals of OOP are often not clear. Some say it is increased code-reuse, other's say it is better run-time detection of problems, and still other's say it is over-all quality. (All these except run-time checking lack any objective proof, by the way.)

The benefits of Control Tables over OOP are fortunately more clear. Control Tables centralize business and application decision logic in one, compact place--the Control Table. Control Tables allow one to take a "fly-over" view of categories (classes) and related operations and/or properties. Add the ability of a table browser to sort, search, and filter by rows, columns, etc., and Control Tables are a very concise, flexible, and visual way to manage a project or abstraction structure. Human eyes and minds can easily comprehend (at least) 2 dimensions, why not take advantage of it? One-dimensional Comb-code is silly.

OOP simply favors one linear dimension at the expense of another. It is true that most OOP languages and tools (currently) provide more run-time error checking, but is this the only goal of an organizational paradigm? Is this worth the loss of a dimensional perspective? In some application domains it may be. However, Control Tables deserve an equal place as an alternative to linear comb code (OOP and traditional procedural).

It is true that some OOP tools, such as "Class Browsers", offer fairly dynamic views of OOP classes. However, such tools can apply to and be built for just about any paradigm. Second, they still tend to show too much "packaging" instead of what is truely unique (significant) about each item. For example, an OOP class viewer may show something like:

```  SubClass Circle {
Method Draw: X(1,2,3)
}
```
Note that this assumes there are other methods or properties of Circle, but they are suppressed by the viewer.

In a Control Table, stuff like "Subclass Circle" and "Method Draw" would not have to be shown, only X(1,2,3). This is because "Circle" and "Draw" appear only once in the row and column descriptors, where they apply to multiple cells. An OO class viewer would most likely repeat these packaging items for each "cell" (sub-window). Thus, Control Tables (CTs) remove the distracting, repetitious packaging and only show the core stuff of the logic in an easy-to-navigate grid. The idea is to isolate what is different about each item, rather than get lost in a sea of formal and repetitious OO declaration packaging. Further, it would take a lot of manual effort to line up the OO class windows by corresponding methods and subclasses.

It can be said that Control Tables "factor out" the repetition of the packaging. In mathematical terms, factoring out would turn:

```    xa + xb + xc + xd

Into:

x(a + b + c + d)
```
which is very similar to what Control Tables do with the declaration packaging mentioned above.

Here are some typical operations that a table browser could do on Control Tables:

• Double-clicking on a cell could open up an edit or view window of the code of the routine referenced in the cell. If more than one function is present in a expression, then either a pick-list could appear, or the first function referenced would be shown. (It is not recommended that a complete function itself be implemented in a CT cell.) Although code viewing is not built into most existing table browsers, it is fairly easy to add such an operation in the likes of VB or Delphi.

• Double-click on any column header in the control table to sort on that column. This allows one to find similar methods or properties. MS-Access performs a similar function by first having the user select the column, and then pressing the "A/Z" icon. Many table browsers also allow sorting by multiple columns, but this requires more complex mouse-work.

• "Lock" a column or set of columns in place. This allows one to view other columns without losing their view of related information. This is often done with description columns so that one knows which row is being studied. (Note that the rows do not need to lock also, just the column(s).)

• Slide two columns together for side-by-side comparing.

• Narrow the width of one or many columns to allow more to be viewed. Widening can also be done when needed.

• Use a "filter" to view only rows fitting a certain criteria. For example, in a data dictionary one may want to view only those rows for required (mandatory data entry) fields. When you are done you simply turn off the filter to see all rows again.

### When to Use Control Tables

Control Tables are best used when there are roughly 15 or more sub-categories (subclasses) and when many or most of the columns are simple codes or short expressions or function calls. If you find yourself working with 100 or more sub-categories, then Control Tables should certainly be considered. Pages and pages of comb code is a good sign that Control Tables may help.

If the Control Table would be mostly sparse for most columns, then perhaps some other organizational paradigm should be considered. Some sparsity for some columns is common. See note below. (Such sparsity often indicates a weedy OO inheritance tree, which perhaps should not be attempted either. It is often a sign that too much effort is being attempted to squeeze out organization that is barely there to begin with.)

### Miscellaneous Notes About Control Tables

It is customary to put the more populated columns on the left side and the sparser ones on the outside. Perhaps a table browser could be configured to do this automatically. Empty cells indicate either a default behavior or a non-applicable operation. We used asterisks in some of our examples, but blanks could serve the same purpose. Thus, empty cells do not necessarily indicate sparsity.

Although no known system can currently check the syntax of expressions in tables, there is no technical reason why a control table cannot be checked by a pre-compiler or syntax checker.

It is not recommended that a complete function itself be implimented in a CT cell. Normally, any expression inside a control table only calls functions, not defines them. (In the future, with better table-oriented IDE's, I may change this rule of thumb.)

Some people claim that empty cells "waste space" on disk and memory. However, this is highly implementation-dependent and often not the case at all.

```  Record 1:  ID=16,price=32.95,descript="Green Shoes",categ="29-A"
Record 2:  ID=17,descript="Custom Shoes",categ="17-M"
```
Record 2 has no price given. Many DB's actually store data somewhat in this fashion. A query on Record 2's price would return a null (or blank or zero) value. IOW, a DB system does not need to set aside ANY space for empty fields. All it needs is a convention for the meaning of non-specified fields. (The schema, or available field list, is stored elsewhere.)

## Another Example

A lot of shrink-wrapped software sold now has what seems like several hundreds of options and settings. These could be represented as a table structure something like this:
```   Title - Description of setting

Group - Which other settings its to appear with

Mandatory - Whether or not appearance of other group
members is mandatory when displayed.

Value  -  Current value of this setting

Choices - List of choice values, such as "on,off"
or "low,medium,high" etc.

Function - Post-validation function that executes

Persistent - Yes, if to be saved when application
is closed.
```
It is true that you can allocate a bunch of objects with the above properties, but this has three problems.

First, it generates an awful lot of program lines which are hard to see and rearrange (the 1D vs. 2D issue).

second, you would have to build a mechanism to store this to disk if you want save the settings between application runs. With ITOP its already stored to disk as a table (although temporary tables are an option).

Third, functions calls cannot be saved to files in many OOP languages.

Beyond just the providing a way to implement settings, such a structure would make it simpler to have setting dialog screens that are more dynamic. For example, the most frequently accessed settings could be placed near the "top", if there is even a top to speak of. The user could see a list of settings and sort them by frequency of use, alphabetic order, the order of the value, the group, or rank them by their fitness to given key words.

Current menus and dialog boxes seem more and more arbitrary these days. In one brand I can find "Options" under "Edit", it others its in "Tools". Menus with titles like "Go" are almost meaningless. If they are going to be arbitrary, then why not make the more fluid? When speech technology becomes mainstream, I bet the physical position in menus and dialog boxes will be considered irrelavent.

Of course this is more of a UI issue than an implementation issue, but it shows how to free up your thinking beyond hardwiring everything in one place.

## Actual Case Studies

The next two cases are based on actual uses of control tables that I have witnessed in use by others. The programmers did not know me at the time. (I have simplified the scope here for illustration purposes.)

### Report Specification Table

Title - title of report
SQL - sql expression with substituters (described later)
Prompt1 - prompt of first substituter (optional)
Prompt2 - prompt of 2nd substituter (optional)

An example table:

Title SQL Prompt1 Prompt2
Sales Summary by Quarter Select year,quarter, sum(amt) from sales where year = @1 and quarter = '@2' group by year, quarter Year Quarter
Summary Sales by Date Range Select sum(amt) from sales where invDate between '@1' and '@2' Start Date Finish Date
Detail Sales by Date Range Select * from sales where invDate between '@1' and '@2' Start Date Finish Date

The user is presented with a list of reports based on a listing of the Title field (they do not need to see the SQL). When they select a report, any prompt values are first requested. In our first example row, the user is prompted for the year and quarter. the values given by the user is then substituted for the "@1" and "@2" parameter specificers. (You may want to have the parameters in a second table if there will be many of them.)

Note that the actual system had many more options, including field picker, and others. Also, the table used Clipper (XBase) expressions instead of SQL expressions, but the concepts are nearly identical.

See Report Example Challenge for a far more detailed version of this kind of tool.

### Parts List Parser

This company received parts lists from several hundreds of sources. To help parse the input data (usually ASCII text), a table resembling the following was used:

InPath - file input path
Fields - fields coming in
FmtType - type of input file (C=comma delim., T=tab delim., X=XML, etc.)
PreFunc1 - preprocessing function 1 (optional)
PreFunc2 - preprocessing function 2 (optional)
PostFunc1 - post-processing function 1 (optional)

InPath Fields FmtType PreFunc1 PreFunc2 PostFunc1
/foo/file4.txt desc, partno, classif, price T * * *
/bar/file_m.txt partno, classif, desc C * * ryon7
/bar/124121.txt price, partno, desc C * Ridstuff_4 *
/zog/xfslkj.dat partno, desc, price T * * *

The 'Fields' column was a string that specified the order and presence of the input fields. Not all fields were given in many cases because either the price was supplied in a later table which had only part number and price, or the classification was guessed later because the supplier had no classification system.

An actual system may use smaller abbreviations or single letters to simplify the interpretation. For example "CPDN" could mean "classif, price, desc, partno". Another alternative is to have dedicated columns for each of these. A number would then indicated the order, with zero meaning that it is not included. Ideally, another table should be used if the fields are open-ended, but that could perhaps be considered over-kill in this situation.
The optional custom functions represented different stages in the processing. Prefunc1 was meant to handle the text stage, before parsing into columns. For example, it might clear out special characters that would confuse the standard import routine.

Prefunc2 was meant to handle issues with the extracted "work" columns. For example, if the description was too long, it might truncate or abbreviate it, depending on the best course of action for that particular supplier. (The work columns were larger than the target table columns.)

Postfunc1 did any final processing to the records AFTER they were copied into the target pricing table. For example there might be consistency checks done because a particular supplier may keep making a certain mistake.

The concept of "stage" seems to be a common theme in many control tables. Control table "stage" columns correspond with key stages or events that occur in a process. (Although in Data Dictionaries, "events" may be a more appropriate description rather than "stage" because the events may not depend on sequenced steps. A double-click event is an example.) The asterisks in our example simply mean that no special function is needed for a given stage. We could have also used blanks instead.

The people who maintained the large table knew only rudimentary programming. They left the occasional writing of the custom routines to a dedicated programmer. However, they were able to maintain the table without having to dig in code very often because most input files did not need custom routines.

### Website Management

This allows the navigation hierarchies and content of a website to be managed primarily via a table.

Field Description
Indent_level Indentation level. Zero for primary level, 1 for next level, etc. An alternative is to use a Parent_id, but the indentation approach seems to work smoother for smaller sites.
Sequence The ordering in the menu. Decimal values allowed.
Title_only (Yes/No) - Yes, if this topic is only a section heading and does not lead directly to a content page. Clicking on such a title may lead to a semi-summary page that lists the topics under it, or perhaps simply not have it be a hyperlink. (Thus, it could be 3 choices instead of a Boolean.)
Content Optional content to be stored directly in the table, such as an announcement.
Content_file Optional reference to an HTML page.
Script Optional name of script to run if title is clicked on.
Show_menu (Yes/No) - Sometimes you don't want the site menu to appear on the page to give you enough room for wider content. (Default: "Yes")
Icon Icon URL for this description.
Description A longer description of this section. May be used for semi-summaries (see "Title_only"), roll-over text, etc.
NOTES: It could be possible to have "content", "content_file", and "script" apply to the same menu option. I recommend displaying them in this same order if such is done.

### Where to Find More Examples

The P/R Patterns page has some more table examples that you might find interesting. Also check out the Email Table example and the Challenge page.

## Going All The Way

Rather than some of the code be in control tables, perhaps all the code of a related group of subclasses could be in a control table. Click here to read about such ideas.

## Thinking "Tablely"

Thinking in terms of ITOP is more than just storing data, it is using tables to organize programs and program logic. Often big programs are nothing more than repeated code with slight variations. Rather than store this similarity in code, putting it in tables makes it easier to organize because tables are 2D and code is 1D. This was demonstrated numerically in our Data Dictionary example.

Not all software can be organized this way, the same way that OOP cannot improve all types of applications. But the opportunity arises fairly often if you know what to look for. We already gave examples of a Data Dictionary control table, "class" control tables, a menu/option control table, and a GUI component control table on this page or in links. (Note that the Data Dictionary is often sufficient for field/screen input specifications, avoiding the need for a seperate GUI control table.)

Just like OOP, it takes time and practice to learn how to organize software using control tables, but will eventually pay off in software that is easier to maintain. Unlike OOP, tables are a familiar organizational concept. Just look at a city bus schedule.

Back | Bank Example | Shapes Example | Publications Example | P/R Patterns (GOF) | Brainstorm
Think Tablely!

Updated: 10/4/2002, © 1999...2002 by Findy Services and B. Jacobs