Physical Database Design-Qualifying columns for valid values

I. Description of the problem

In fact, this article is very simple, because everyone is solved by this method, I decided to use their own language to describe the problem clearly.

Suppose we have a column that can only fetch certain valid values. For example, a user table, we have a last Name column, we need to limit the value inside the Chinese surname, such as: Zhou Chansunli Lingwei romanization.

second, anti-pattern

For this problem, only beginners may use this method, that is, using a check constraint or trigger to qualify a column value, such as:

　　CHECK (LastName in (' Zhao ',' qian ', ' Sun ', ' Li '

The disadvantages of doing this are as follows:

1, it is difficult to get all the optional values, if I want to make a drop-down list, let the user choose the last name that can be entered, then the SQL statement is complex, you need to query the system view.

2, add the optional value, if we want to add a foreign surname. Then you need to modify the check constraint or trigger.

3, delete the optional value, assuming that from today does not support foreign surnames, but the database has already had a foreign surname, you have to keep this obsolete value. But the drop-down list box is no longer available to the user, which is a big hassle.

4, transplant poor, CHECK, constraint, trigger syntax in a variety of databases are not the same, the transplant is very difficult.

Third, the solution

Create a checklist with each row containing the values that allow the last name to appear, and then define a foreign key constraint.

This you know, no more nonsense.

Unless you are sure that these values will not change, you can do so by using a check constraint or a trigger. For example, Gender: male, female. Otherwise, it is better to use a checklist.

Physical Database Design-Qualifying columns for valid values