SQL anti-Pattern Learning Note 6 supports variable attribute "entity-attribute-value"

2014-10-11 17:21:31

target: supports variable attributes

anti-pattern: use a generic attribute table. This design becomes an entity-attribute-value (EAV), also called an open schema, a name-value pair.

Pros: By adding an extra table, you can have the following benefits

(1) There are few columns in the table;

(2) New columns are not required when adding properties. Does not affect the structure of existing tables;

(3) The Stored field contents will not be null values.

Disadvantages: (1) Query statements become more complex;

(2) After using EAV design, we need to abandon the convenience of traditional database design, such as: unable to guarantee data integrity;

(3) Unable to use SQL data type, such as date, money and other format content can only be maintained as a string type;

(4) Cannot ensure referential integrity;

(5) The property name cannot be configured. For example, there might be two records in the table,

One of the attr_name is sex, a attr_name is gender, is the expression of gender;

(6) When there are multiple attributes in the query result, the query is very difficult and the query performance cannot be controlled.

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

(1) The database does not need to modify the metabase (the column attributes in the table) to be extensible. You can also define new properties at run time.

　　(2) The query is a very large number of connections, and the number of connections may reach the limits of the database, your database design may be problematic.

(3) The common report query becomes complicated and even impractical.

Rational use of anti-patterns:

(1) The use of EAV in relational databases means that many of the advantages of relational database paradigms are discarded.

However, this does not affect the proper use of this design in some programs to support dynamic properties.

(2) If there is a need for non-relational data management, the best way to do this is to use a NoSQL database.

The disadvantages of using EAV designs in traditional databases are also reflected in these non-relational databases. When metadata does not have a fixed format,

A simple query can become very difficult. Upper-level applications take more time and effort to organize data structures.

Solution: modeled subtypes

1. Single-table inheritance: All attributes are saved on a single table, and the table is expanded when attributes are added.

Single-table inheritance can be used when the subtype of the data is small and the special attributes of the subtypes are rare.

Cons: (1) When a program needs to join a new object, the database must be modified to accommodate these new objects. And because these new objects have some properties that are not used by old objects,

Therefore, you must add a new attribute column in the original table, you may encounter a practical problem, that is, the number of columns per table is limited.

(2) There is no meta-information to record which property belongs to which subtype.

2. Entity Table Inheritance: Create a separate table for each subtype, each containing a common attribute that belongs to the base class, and also a property of subtype specialization.

Advantages: (1) The advantage of the design of the entity inheriting class is that it provides a method for the design of the table inheritance.

Lets you organize to store some properties that are not related to the current subtype in a row.

If you refer to a property column that does not exist in this table, the database will automatically prompt you for errors.

(2) Do not use extra attributes to mark subtypes as you would in a single-table inheritance design.

Disadvantage: It is difficult to differentiate between common attributes and subclass-specific attributes. Therefore, if you add a new attribute to the common property,

You must add the table for each child class again.

Entity Inheritance table design is the best choice when you rarely need one-time queries to have subtypes.

3, Class table inheritance: The table as an object-oriented class.

Creates a base-class table that contains the public properties of all subtypes. For each subtype, create a separate table that is connected by the foreign key and the base class table.

4. Semi-structured data model: If you have many subtypes or you must frequently add new attribute support, you can use a BLOB column to store the data.

In XML or JSON format-colleagues include the name and value of the attribute. This is called serialization of large object blocks.

The advantage of this design is extensibility, and the disadvantage is that SQL cannot get a specified property in such a structure. You have to either the entire BLOB field and the program to interpret the attributes.

When you need absolute flexibility, you can use this scenario.

If you use EAV, you can first take all of the attributes out and then do other processing.

Conclusion: SQL has provided a way to explicitly define attributes-in explicit columns. That is, metadata is used for metadata.

　　

SQL anti-Pattern Learning Note 6 supports variable attribute "entity-attribute-value"