SQL anti-Pattern Learning Note 11 qualifying columns for valid values

2014-10-14 08:46:07

Goal : Qualify the valid values for the column and constrain the valid field values of a column to a fixed set. Similar to a data dictionary.

anti-pattern : specifying an optional value on a column definition

1. Define a CHECK constraint entry for a column that does not allow inserting or updating any values in the column that would cause the constraint to fail:

CREATE TABLE Bugs (status varchar check (' new ', ' in progress ', ' fixed ')).

2, using domain or user-defined type (UDT) and other methods.

3. Use a trigger: write a trigger that fires when the contents of the specified column are modified, matching the modified value to the value that is allowed to be entered,

An error interrupt operation occurs if the non-conformance occurs.

　Disadvantages:

1. All constraint values cannot be queried for use by upper-level applications.

You cannot use SELECT distinct status from Bugs because there may be status that is not currently stored.

If maintenance is not good, it is possible to create inconsistent values for the list and database storage.

2, add new constraint value, need to modify the database definition or trigger.

3, discard or modify a value, you may have to modify a large amount of data, the risk is very large and unreasonable.

4, the portability is poor, check constraints, domain, or UDT in various databases in the form of support is not uniform.

how to recognize anti-patterns : May be anti-pattern when the following conditions occur

1, we have to put the database offline, in order to add a new option in the program.

2. This status column can fill in one of these candidate values. We should not change this list of post-selection values.

3. The list of options in the program code about business rules is not synchronized with the values in the database.

Rational use of anti-patterns :

1. Can be used when the post-selection value is hardly changed.

2. It is convenient to store candidate values that do not have business logic and do not need to be changed.

For example, store a pair of two pairs of all opposing values: Left/right, ON/off, and so on.

3, check constraints can be used in more scenarios, such as to detect a time interval in the start is always less than end.

Solution : Create a checklist (similar to a dictionary table) with each row containing a candidate value that is allowed to appear in the column, and then define a foreign key constraint in the target table.

Personal experience: Create a data dictionary table in the system to manage all mutable candidate collections.

Conclusion : Metadata is used when validating the candidate values of a fixed set;

The data is used when validating candidate values for a mutable collection.

SQL anti-Pattern Learning Note 11 qualifying columns for valid values