Section 1.6. Stating the Obvious

1.6. Stating the Obvious

It is always an unsound situation in which there are implicit constraints on your datafor instance "if the business line is such, then the identifier is numeric (although defined as a string of characters to accommodate other business lines)," or "if the model is T, then the color is necessarily black." Sometimes, such general knowledge information can prove extremely efficient when filtering data. However, if it remains human knowledge, the DBMS engine, unaware of it, will be unable to take advantage of it, and the optimizer will not possess the necessary information to affect the most efficient database access. In the worst case, implicit constraints can even lead to a runtime failure. For instance, you might inadvertently require the database engine to apply an arithmetic process to a character string. This can happen when a character-defined column is used only for storing numeric data, and a non-numeric character slips in.

As an aside, the example of a string identifier that sometimes contains character data and sometimes numerical data illustrates a confusion over domain definitions in the initial database design. It is quite clear that the nature of such a field varies according to circumstanceswhich is totally unacceptable in a properly designed database. If we need to store, for instance, configuration parameters of various natures (numerical, Boolean, character, and so on), we should not store them in a single table configuration(parameter_name, parameter_value), but rather use a generic table configuration(parameter_id, parameter_name, parameter_type) and have as many subtypes as we have parameter types. If we use, for instance, configuration_numeric(parameter_id, parameter_value), where parameter_value is a numeric column, any mistyping of the letter "O" instead of zero will be detected by the DBMS when the configuration is changed, instead of resulting in a runtime error when the parameter is used.

Define all the constraints you can. Primary keys are, of course, a sine qua non in a relational database. Use alternate key, when they characterize the data and any type of unique constraints. Foreign keys, which ensure that your data is consistent by mapping to master tables, are vital as part of the comprehensive expression of the meaning of the data model. Constraints that control the range of values that can be entered are also valuable. Constraints have two major impacts:

• They contribute to ensuring the integrity of your data, guaranteeing that everything, as far as defined rules are concerned, is consistent with those rules.

• They provide valuable information about your data to the DBMS kernel, and more specifically to the optimizer. Even if today the optimizer does not make full use of all available constraint data, it is likely that in future releases of the database system, that constraint data will become used for more sophisticated processing by the kernel.

The earlier example of the confusion over multiple shipping and billing addresses is a further example of the way semantic information is lost to the database by a fundamentally weak design. This essential information must therefore be placed into an unpredictable number of application programs. "If the billing address is null, then the headquarters address applies" is a rule that is unknown to the database and must therefore be handled in the programsnote the use of the plural programs here! Once again, everything that is defined in the database is defined only once, thus guaranteeing that no program will use the data inconsistently. Implicit rules about, for example, address precedence must be coded into every program accessing the data. Because these implicit rules are totally arbitrary, it is not impossible at all that in some cases the billing address will be the shipping address, and not the headquarters address.

Data semantics belong in the DBMS, not in the application programs.