Click to learn more about author Michael Blaha.

Database applications typically have a direct representation. Application concepts are represented as tables with their attributes stored as table columns. This direct approach works well for applications with fixed structure. However, it fails for applications with structure that is not fully known as the application is being built.

Softcoding addresses this problem with a generic mechanism for defining and storing data at run time. With softcoding there are tables that store the description of data in addition to tables that store the actual values.

A Softcoding Data Model

Here’s a basic model for softcoded data. The blue color denotes metadata and the yellow color denotes data. In practice, there are many variations on this basic model.

An Entity is a placeholder for things that can have softcoded Values. A Value is a piece of data for an Entity and handles different data types with parallel fields. A Value can have one of three data types—integer, date, or string. Exactly one of these three fields must be filled in (and the other two set to NULL) for each Value record. If additional data types were needed (such as money), it would be a simple matter to add more fields to Value.

An Entity_Type defines a group of Entities. An Entity_Type can have many Attributes. An Attribute is a named property of an Entity_Type that describes Values held by each Entity of the Entity_Type. Each Attribute has a specified data type (integer, date, or string) indicating the appropriate field to fill in for each Value. Attributes with a data type of ‘string’ have a maximum length to which Values must conform.

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

Note that the model permits an Entity and Attribute combination to have no Value or multiple Values. The min multiplicity indicates if an Attribute is optional (min multiplicity = 0) or required (min multiplicity = 1) for each of the Entities in an Entity_Type. Similarly, the max multiplicity indicates if an Attribute is single valued (max multiplicity = 1) or multi-valued (max multiplicity = *) for each Entity in an Entity_Type.

The model also permits a history of Values for an Entity.

Trade-Offs

When we discuss softcoding with developers, they often focus on performance. They observe that more records are being processed and conclude that the database will be slow. However, their concern is often a misunderstanding. Softcoding can have little performance impact, if implemented properly. For example, clustered indexes can physically group values by object and greatly speed access.

Softcoding can complicate, or simplify, development. Softcoding complicates development by introducing metadata. Developers not only must think about application data, but must also think about metadata. This can lead to SQL queries that are prepared at run time and that means more complex code. With softcoding developers must write code to enforce constraints that would otherwise be handled with a hardcoded data structure.

However, for an application with a large taxonomy, softcoding can radically reduce the number of tables. Fewer tables mean faster development. For example, the data model for a manufacturing plant could have many equipment tables. In the extreme case, hundreds of equipment tables could be replaced by a handful of softcoded tables.

In Conclusion

The bottom line is that softcoding is a valuable development technique, but should only be used when needed. For an application that requires flexibility, the added complexity can be well worthwhile.