SQL anti-Pattern Learning Note 8 Multi-column Properties

2014-10-13 10:32:12

target : store Multi-valued properties

anti-pattern : Create multiple columns. For example, a person has multiple phone numbers, landline numbers, mobile phone numbers and so on.           

1, query: Multiple columns, query may have to use in, or more or;

2, add, delete to ensure uniqueness, determine whether there are values: these are very troublesome;

3. The number of columns in the field cannot be determined.

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

1. What is the number of dynamic columns that should be supported?

2. How can I query multiple columns at the same time in a SQL query?

Rational use of anti-patterns :

In the case of dictation, a property may have a fixed number of candidate values, and their storage location and order are fixed. In this case, you can use anti-patterns.

Solution : Create a subordinate table that uses only one column to store multivalued properties.

Define a foreign key in the subordinate table that associates the value with the primary key ID in the target table.

conclusion : Stores values that have the same meaning in the same column.

SQL anti-Pattern Learning Note 8 Multi-column Properties