Chapter 13

Softcoded Values

Most database applications directly map concepts to tables. The direct approach is effective for applications with well-defined entity types and attributes. However, it fails for applications with open-ended requirements.

Consider person data. Persons are prominent in applications and can involve much data. For example, a police database could have a lengthy description of suspects including height, weight, eye color, and hair color. The top of Figure 13.1 shows a database table for Person, using a direct representation (hardcoding). The bottom shows an alternative representation using softcoding. The columns in boldface are primary keys.

If the requirements are uncertain, the hardcoding of person data will be fragile. New attributes will be likely to arise, requiring model extensions and disrupting the application code. Softcoding decreases efficiency and increases storage space, but the cost is tolerable for many applications.

Hardcoding builds many constraints into the database structure. For example, a person has one height and one weight. With softcoding, metadata can declare that each person has a single value of height and weight. (Figure 13.1 omits metadata.) Careful programming must ensure that data satisfies the constraints specified by the metadata.

Hardcoding is easier to visualize and develop. Softcoding involves queries and programming that are more complex. Given that applications hide behind a user interface, softcoding implies more work for the developer, but need not complicate the application for the end user. The database and application code can be softcoded for flexibility with the user interface being hardcoded for usability.

13.1 UML Model

Figure 13.2 presents a UML model for softcoded values. The model combines data and metadata. The top entity types shaded in gray (EntityType, Attribute, EnumValue) concern metadata. The bottom (Entity, SoftcodedValue) concern data. Ordinary users may enter data but only privileged users should enter metadata. Softcoded values extend the Homomorphism template. (See Chapter 5.)

13.1.1 Data

An Entity is a placeholder for things that can have SoftcodedValues.

A SoftcodedValue is a piece of data for an Entity and has parallel fields for different data types. A SoftcodedValue can have one of four data types—integer, decimal, string, or date-Time. Exactly one of these four fields must be filled in (and the other three set to NULL) for each SoftcodedValue record. If additional data types are needed (such as money), you can add more fields. Note the following subtleties of the model.

• Multivalues. An Entity can have multiple SoftcodedValues for the same Attribute (Attribute is to be explained).
• Flexible data entry. By default, any SoftcodedValue can be stored. Alternatively, you may restrict the possible SoftcodedValues with enumerations (to be explained).

13.1.2 Metadata

An EntityType describes a group of Entities with the same attributes, behavior, kinds of relationships, and semantics. An EntityType can have many Attributes. An Attribute is a named property of an EntityType that describes SoftcodedValues that can be held by each Entity of the EntityType.

Thus each Entity has an EntityType that defines Attributes for which SoftcodedValues can be stored. Another way of thinking about the model is that an Entity can have any number of SoftcodedValues. The EntityType constrains the SoftcodedValues by specifying the Attributes that can be populated.

Each Attribute has a specified dataType (integer, decimal, string, or datetime) indicating the appropriate field to fill in for each SoftcodedValue. Attributes with a dataType of ‘string' have a maximum length to which SoftcodedValues must conform.

The box enclosing attributeName is a UML qualifier specifying that each Attribute has a unique attributeName for its EntityType. Since the model does not specify otherwise, an attributeName is not globally unique and may be reused across multiple EntityTypes. The Attributes are ordered within an EntityType just as you would find for a hardcoded model.

Note that the model’s structure permits an Entity and Attribute combination to have no SoftcodedValue or multiple SoftcodedValues. The minMultiplicity indicates if an Attribute is optional (minMultiplicity = 0) or required (minMultiplicity = 1) for each Entity of an EntityType. Similarly, the maxMultiplicity indicates if an Attribute is single valued (maxMultiplicity = 1) or multi valued (maxMultiplicity = *) for each Entity of an EntityType. The maxMultiplicity is intended to be a set—if an Entity has multiple SoftcodedValues for an Attribute, they are not ordered and there can be no duplicates.

Some Attributes have a set of possible values. For example, the permissible colors for a lawn mower might be red, green, and blue. An EnumValue defines an enumeration value for an Attribute. If EnumValues are specified for an Attribute, the SoftcodedValues must conform to the list. The EnumValues for an Attribute have a defined order.

EnumValue also has parallel fields for the different data types. An EnumValue can have one of four data types—integer, decimal, string, or dateTime—corresponding to its Attribute. Exactly one of these four fields must be filled in (and the other three set to NULL) for each EnumValue. If additional data types were needed (such as money), it would be a simple matter to add more fields.

13.2 IDEF1X Mode1

Figure 13.3 restates Figure 13.2 with the IDEF1X notation. The IDEF1X model uses existence-based identity. (See Chapter 16.)

An Entity can have one or more fields that serve as a logical identifier (alternate keys, see Chapter 11). For example, taxpayer number could identify Persons. Given a logical identifier, a user can find an Entity and then traverse the model to retrieve associated data.

13.3 Architecture

13.3.1 Using Softcoded Values in an Application

There are two ways that an application can use softcoded values.

• Inherit softcoding. In Figure 13.4 and Figure 13.5 Person and Document inherit from Entity. Thus, for example, all Persons are Entities with an EntityType of “Person” that determines Attributes for which softcoded values can be stored. [B1aha-2006] shows how to write Entity stored procedures that Person and Document can reuse. The example shows only Person and Document, but the inheritance approach is best when several EntityTypes have softcoded values.

• Clone softcoding. Figure 13.6 and Figure 13.7 clone softcoding tables, once for Person and once for Document. With this approach an application not only clones schema, but also repeats data manipulation logic. Cloning provides a simple approach when few entity types have softcoded values.

13.3.2 Hardcoded Model for Populating Metadata

Metadata can be difficult to grasp. It is often helpful to first prepare a hardcoded model (such as Figure 13.8) as an intermediate step and use it to populate the metadata. Domain experts understand hardcoded models better, because they can see the concepts, relationships, and constraints.

13.3.3 Mixing Hardcoded and Softcoded Attributes

Application entities can have both hardcoded and softcoded values. For example, the name and taxpayer number of a Person may be hardcoded. Miscellaneous data (such as weight, height, eye color, and hair color) can be softcoded. Figure 13.2 and Figure 13.3 define softcoded attributes. In contrast, each hardcoded attribute becomes a field in an application entity type.

Since there are two ways of defining attributes, there must be a way for choosing between them. My convention is to hardcode system attributes and softcode other attributes, as Table 13.1 explains.

Given the rationale for softcoding, it is clear that you should softcode attributes that may not be known until run time. For simplicity and improved performance, you can hardcode attributes that are certain.

Some attributes have a critical effect on queries and if you hardcode them you can improve performance. In particular, database indexes for important hardcoded values can boost performance by orders of magnitude. Softcoding is not amenable to database indexes because values from different entities and attributes are mixed together.

Softcoding can be desirable when there are multiple values for an entity-attribute combination. Relational database tables are flat, and hardcoding consequentially limits an entity-attribute combination to at most one value.

Softcoding also facilitates value metadata. Each value has its own record so it is easy to attach incidental data. In contrast, value metadata is clumsy with hardcoded values. For example, Figure 13.8 could have the additional attributes heightDataSource, heightUnitsOfMeasure, heightLastUpdate, weightDataSource, and so forth. The number of added attributes can quickly become unwieldy.

13.3.4 Database Performance

You do have to pay attention to performance to obtain good softcoding results.

Softcoding disperses the values of an entity across multiple records and it is important to avoid database thrashing. Therefore if you update several fields for an entity, it is normally best to enclose the updates within a database transaction. Then the group of records incurs the transaction overhead once, rather than for each update, one at a time.

Another database technique is to collocate records for an entity on the physical disc. Many DBMSs (such as Oracle and SQL Server) have commands to define the physical ordering of records for a table. If you organize the disc so that an entity’s softcoded records are physically contiguous and define transactions as described above, disc activity is comparable to that of a hardcoded record type.

13.3.5 User Interface

Softcoding in a database does not imply softcoding in a user interface. As Figure 13.9 shows, it is best to adopt a hybrid approach where known attributes (both hardcoded and softcoded) have specific placement on a screen and miscellaneous softcoded values are in a generic panel. This gives you the best of both worlds—a softcoded database handles volatile data; hard-coded screens ease user understanding. Then a change to softcoded values does not affect database structure, but could require user interface revisions. Given that it is much easier and less disruptive to change a user interface than a database, the combination of a softcoded database with a hardcoded user interface can be an effective compromise.

13.4 Softcoding Variations

Figure 13.2 and Figure 13.3 provide one approach to softcoding, but others are possible.

13.4.1 Add Value Metadata

One rationale for softcoding is support for value metadata. By promoting SoftcodedValue to an entity type, a model can readily store information about it. For example, each value has a source — whether it is obtained from a person, the literature, calculations, an estimate, or somewhere else. Some values may have a unit of measure (such as inches, meters, seconds, and joules) — a value may override the default for its attribute. A value can also have a time of entry. Figure 13.10 and Figure 13.11 show such a revised model.

13.4.2 Cut Metadata

A simplification is to forego metadata. This is essentially what the UML does with tagged values. Figure 13.12 and Figure 13.13 show the corresponding revision. In essence the model stores values without metadata. The advantage of omitting metadata is simplicity. The downside is that the values are less structured, less controlled, and more vulnerable to errors. This approach can be useful for a small amount of miscellaneous data, but is not suitable for large quantities.

13.4.3 Store All SoftcodedValues as Strings

Figure 13.14 and Figure 13.15 show the option of storing all values as strings and converting them to the appropriate data type upon access. This simplifies the model at the cost of type conversions. Type conversions complicate programming and can introduce round-off errors.

13.4.4 Subtype by Data Type

An implementation can subtype SoftcodedValue, Attribute, and EnumValue according to the dataType (Figure 13.16, Figure 13.17). Note that the model’s structure does not enforce that SoftcodedValue and Attribute subtypes must correspond. For example, ValueInteger should only be associated with AttributeInteger. Programming code must enforce this intent.

13.4.5 Require All Attributes to be Enumerated

Another possibility is to require that all softcoded values be enumerated (Figure 13.18, Figure 13.19). Enumerations control data entry so there should be approval for new EnumValues. The resulting database has clean data since users can not introduce variations such as alternate spellings, abbreviations, and upper/lower case combinations for the same thing.

13.4.6 Enable Time History

The previous softcoded value models have treated data and metadata as invariant. An extension is to let data change over time. For example, each SoftcodedValue could have effective and expiration dates to track its history. Then a database can record a SoftcodedValue in advance of when it is needed. It can also keep a SoftcodedValue after it becomes obsolete. Similarly, Attributes and EnumValues could have effective and expiration dates.

The addition of time history greatly complicates the model, more so than it might seem at first. For example, what is the meaning of minMultiplicity when SoftcodedValues change over time? If minMultiplicity is ‘0’, there is no problem. If minMultiplicity is ‘1’ then a SoftcodedValue must exist for the Attribute at all times, extending indefinitely into the past as well as the future.

Time history also complicates enforcement of maxMultiplicity. If maxMultiplicity is ‘*’, there is no problem. If maxMultiplicity is ‘1’ then software must ensure that the constraint is satisfied at all times. The enforcement of minMultiplicity and maxMultiplicity leads to tricky programming that can degrade performance. Also, it is difficult to explain errors. In practice, it is better to omit minMultiplicity and maxMultiplicity from a model with time history.

Another concern is coordinating Attribute time intervals with SoftcodedValue time intervals. An Attribute’s change cannot conflict with its SoftcodedValues. For example, a reduction in the maxLength for a string cannot conflict with pre-existing SoftcodedValues. Some changes are not permitted. For example, if SoftcodedValues are stored it probably does not make sense to change the dataType from dateTime to integer.

Given the semantic questions, performance concerns, development effort, and likelihood of bugs, Figure 13.20 and Figure 13.21 simplify the model with time history. Note that the minMultiplicity is implicitly ‘0’ and maxMultiplicity is ‘*’. The Attribute name can vary. So too can the dataType as long as there is no conflict. Changes to maxLength are permitted.

13.4.7 Support Weakly Typed Entities

The typical application assigns each entity a type that determines its attributes. However, sometimes it is helpful to define an entity on its own and then assign it multiple types that determine sets of attributes that can be populated. For example, there can be different perspectives of persons. For example, person data can include identification, physical description, and personal preferences.

Another way of thinking is to regard entities as independent things. Each entity can be assigned multiple entity types, and the entity types can vary over time. This is the mathematical perspective. Consider a four-sided polygon on a screen (Figure 13.22). The dragging of one vertex can make it a parallelogram. Then the dragging of two vertices can make it into a rectangle. The dragging of a side can turn it into a square. All the time the same entity is being manipulated, but its entity type progressively changes from Polygon to Parallelogram to Rectangle to Square.

Figure 13.23 and Figure 13.24 permit an Entity to have multiple EntityTypes. A EntityType can be added to an Entity. Similarly, an EntityType can be removed from an Entity, if there are no dependent SoftcodedValues. The union of EntityTypes determines the Attributes for which SoftcodedValues can be stored. This model makes Attributes globally unique, so that there is no confusion among the various EntityTypes.

13.4.8 Combine Variations

This chapter has presented the variations individually, but of course, you can combine many of them. For example, you might add value metadata and represent all data as strings.

13.5 Chapter Summary

I know of several industrial applications of softcoded values that have worked well in practice. The resulting applications are flexible and their performance is only moderately slower (about 20% slower) than with hardcoded data. Of course, writing the software was more difficult than with hardcoding and required skilled developers.

You should consider using softcoded values for an application with uncertain data structure. For volatile applications, softcoding adds stability to the data representation, minimizes changes to application logic, and reduces the likelihood of data conversion. A softcoded model is more abstract than a hardcoded model, hence it is more resilient in the face of application changes.

The cost of using softcoded values is additional complexity as well as a modest performance penalty. You must be careful with your use of SQL to obtain good database performance.

Bibliographic Notes

This chapter is based on an IEEE Computer Society ReadyNote (an electronic mini-book) [Blaha-2006]. The ReadyNote has a more detailed explanation of softcoded values and provides a SQL Server implementation.

[Kopaliani-2007] presents a case study of using softcoded values for a library information system. He has also implemented softcoded values using SQL Server and reports excellent performance. Softcoded values provide a robust infrastructure for his employer’s software product architecture in the mechanical parts industry.

References

[Blaha-2006] Michael Blaha. Designing and Implementing Softcoded Values. IEEE Computer Society ReadyNote, 2006.

[Kopaliani-2007] Dimitri Kopaliani. Architecture of Digital Asset Systems for Libraries. Dissertation at Lawrence Technological University, 2007.