<home

The Principle of Orthogonal Design

The Principle of Orthogonal Design (POD) is a database design principle devised by Chris Date and Dave McGoveran^[1]. It is a formalised version of a familiar idea in software engineering, namely "Don't repeat yourself" or "Once and once only". POD tries to define precisely what kind of repetition ought to be avoided in a relational database design. It complements other design principles, such as Normalization, and helps to identify and eliminate problems that won't necessarily be avoided just by satisfying the Normal Forms.

The Principle of Orthogonal Design
Let A and B be two distinct relvars. There must not exist nonloss decompositions of A and B into A1, A2, …, Am and B1, …, Bn (respectively) such that two distinct projections in the set A1, A2, …, Am, B1, B2, …, Bn have overlapping meanings.

What this means is that database tables should not overlap in their meaning either in whole or in part. At its simplest, POD is violated if two distinct relvars (tables) consist of the same attributes. Take the example of a personnel database where each department's employees are held in separate but identical tables.

Underlining denotes the key in each table. Assuming that the functional dependency EmployeeNum → EmployeeName is the only dependency here then we can be sure that the SALES and MARKETING tables satisfy the requirements of 6th Normal Form. In other words the tables are "fully normalized". There are some fairly clear disadvantages to this design however:

• The information about what department an employee works in is represented only by a table name and not as a value in a table, making it harder to answer queries like “What department does Ellen work in?”.

• Any logical operations on the tables will have to reference multiple tables instead of just one, possibly increasing the complexity of queries and other business logic.

• The database schema has to be changed every time we want to create a new department or remove an existing department. Any queries and other logic that need to operate on all employees would then have to be updated for the new set of tables.

• Any table-level constraints that apply to all employees will have to be defined in two places.

• The key constraints alone are not sufficient to enforce the data integrity rule that an employee can only work in one department. If that is intended to be a business rule enforced by the database then an extra constraint will be necessary to enforce it. The absence of such a constraint is actually a subtle violation of the FD EmployeeNum → EmployeeName. Normal Forms 2,3,4,5,6 and BCNF are not violated because those NFs (at least as originally defined) only consider the implications of FDs within a single relation at a time and not across the whole of a database schema.

The obvious remedy is simply to combine the employee data into a single table and add a “Dept” column to identify the department of each employee.

Not all examples of repetition in table designs are so obvious. A similar situation can occur where table structures are not identical but only partially overlap - i.e. some subset of attributes is shared between tables. Tables that are structurally identical are just a worst case example of the more general problem of table structures which overlap. Table structures overlap when two distinct tables share some set of attributes which could be non-loss decomposed from those tables.^[2] If a table satisfies 5th Normal Form then we know that the only possible non-loss decompositions are on sets of attributes which include a key (because any non-loss decomposition not implied by the keys of a table is a violation of 5th Normal Form).

Here's a revised version of our personnel database. The departments have been combined into a single table:

EMPLOYEE

EmployeeNum	EmployeeName	Dept
10185	Doug	M
10251	Ellen	M
10372	Alice	S
10433	Charles	S
10442	Bob	S
10575	Frank	M

This corrects the original problem of the duplicated table structures but now let's assume we need to add two more tables, to record the details of permanent and temporary staff respectively:

The two extra tables exist because the attributes associated with permanent and temporary employees are different. Only permanent employees have a Salary attribute; only temporary employees have a Rate attribute. The StartDate is common to all employees but here it appears in both tables and that could give rise to some of the same disadvantages of our original two-table design. The presence of an attribute in multiple tables doesn't automatically imply a violation of POD because the POD is concerned only with sets of attributes common to non-loss decompositions of more than one table. In this case however, the non-loss decompositions from the EMPLOYEE_PERM and EMPLOYEE_TEMP tables would both include the projection on EmployeeNum and StartDate and therefore this design does not satisfy the requirements of POD. Assuming a set of tables satisfy 5th Normal Form, decompositions that may violate POD are only possible where multiple tables have at least one key shared between them. In the present case the shared key is obviously EmployeeNum.

We can satisfy 5th Normal Form by putting the StartDate attribute into any (or all!) of the three tables we have defined. However, the Principle of Orthogonal Design requires that the combination of EmployeeNum and StartDate can appear only in one place and logically enough that has to be the EMPLOYEE table because StartDate applies to all employees.

POD in its most complete and precise form poses some problems of interpretation and practicality. For example, is it always possible to satisfy POD and if not then what is the determining factor? Are there "good" and "bad" kinds of overlapping tables? When exactly do two identical projections qualify as "overlapping" each other? This last question is something that Chris Date has answered in several different ways over the years and is perhaps still open to interpretation. I'm not attempting to address all the issues here. Even so, the value of POD is that it can act as a useful guide to design issues that are otherwise difficult to define and describe.

[2] In a later refinement of the principle its authors say that two tables overlap in violation of POD only when constraints allow that a given tuple could be inserted into either table. The version I'm describing here is stricter but simpler to explain.