Choosing Physical Tuning Options › Non-SQL Tuning Options › Variable-Length Entities

Variable-Length Entities

Use a repeating element in a variable-length entity instead of two separate entity types when:

• The "many" portion of the relationship does not participate in other relationships. Once you have created a repeating data element, you cannot relate the data in this element to other entity types.
• The number of repetitions is not static. In general, use a variable-length entity when the average number of the entity's repeating groups actually used is less than 75% of the maximum number of repetitions. Otherwise, use a fixed-length entity to store the repeating group. (The 75% figure is a general guideline. You should consider actual disk space savings.) See Refining the Database Design for information on fixed-length entities.

  Note: Each entity can have only one variably repeating data element.

• SQL access to the repeating information is not a requirement.

  If you intend to use SQL to retrieve information from the database, you may not want to create variable repeating data elements because you will not be able to access the variable portion through SQL.

You must include a counter element in the entity to indicate the current number of occurrences of the repeating data element in each entity occurrence.

If you decide to create a repeating data element in a variable-length entity, be sure to change the length of the entity on the data structure diagram. Additionally, change the storage mode of the entity to V (variable).

Several entities in the Commonweather database can be converted to repeating elements in variable-length entities. The NON-HOSPITAL PROCEDURE and DENTAL PROCEDURE entities should be made repeating elements because they each participate in only one relationship and occur a limited number of times:

• The NON-HOSPITAL PROCEDURE entity can be converted to a repeating element in the NON-HOSPITAL CLAIM entity.
• The DENTAL PROCEDURE entity can be converted to a repeating element in the DENTAL CLAIM entity.