Implementing Your Design › Reviewing the Design › Step 2: Review the Physical Database Model › Potential Design Flaws

Potential Design Flaws

As you trace the flow of each transaction, you need to look for potential design flaws. Here are some things to watch out for.

Nonclustered relationships

Relationships between two entities that are stored with the CALC location mode sometimes degrade processing in applications that retrieve all child entity occurrences. When two CALC entities are related, the system must perform several I/O operations to retrieve the child entity occurrences participating in the relationship, as shown below.

CALC-to-CALC relationships are particularly costly for long chained relationships (those having many child occurrences). In the following diagram, note the number of pages accessed in order to retrieve all employees in a particular department.

Sorted relationships

Sorted relationships are efficient for some kinds of processing and not for others. When you design a relationship, you need to consider whether the sorted order is appropriate for the type of processing that will be performed.

Make sure that:

• Every sorted relationship can be justified.
• If new key values are higher than existing values, the relationship is ordered in descending sequence.
• If new key values are lower than existing values, the relationship is ordered in ascending sequence.
• If the relationship is not clustered, it is indexed rather than chained (non-SQL implementation).

For further information on sorted relationships, see Refining the Database Design.

Relationships crossing areas

When two entities related through a linked relationship are stored in different database areas, certain utilities require that you operate on both areas at the same time. Therefore, you might want to consider using an unlinked relationship rather than a linked relationship.

Ineffective clustering

Processing performance can be affected by ineffective clustering. Suppose that an entity participates as a child in two relationships. To achieve optimal performance, the relationship through which an entity is most frequently accessed should be chosen as the clustering relationship.

In the example below, retrieving all positions for a job will require fewer I/Os than retrieving all positions for an employee. This should be reviewed to ensure that it reflects transaction frequencies.

Large clusters

Large clusters of entity occurrences can also cause performance problems. If the amount of space required to hold related entity occurrences is greater than the page size for a database area, CALC or cluster overflow conditions can occur.

Absence of PRIOR pointers in a non-SQL implementation

PRIOR pointers should be excluded from a relationship only when all of the following conditions are true:

• Child entity occurrences in a relationship are not erased or disconnected.
• Child entity occurrences in a relationship participate in no other relationship.
• Order is not LAST or PRIOR.
• The FIND/OBTAIN LAST or FIND/OBTAIN PRIOR functions are not used for the relationship.

In all other circumstances, you should include PRIOR pointers in a relationship.

Absence of OWNER pointers in a non-SQL implementation

OWNER pointers should be excluded from a relationship only when all of the following conditions are true:

• Parent entities in a relationship are not accessed from child entities.
• The FIND/OBTAIN OWNER DML function is not used for the relationship.
• Parent and child entities are normally stored all on one page.

In all other circumstances, you should include OWNER pointers in a relationship. Every relationship must have NEXT pointers except indexed relationships, which must have INDEX pointers.