SQL anti-Pattern Learning Note 7 Polymorphic Association

2014-10-13 09:50:54

target : referencing multiple parent tables

anti-pattern : Use a multi-purpose foreign key. This design is also called a polymorphic association, or a messy association.

Polymorphic associations and EAV have similar characteristics: the names of metadata objects are stored in strings.

In a polymorphic association, the name of the parent table is stored in a single column of Issue_type, and sometimes such a design is called: Mixed data and raw data.

Query Example :

SELECT * from A as a

Left JOIN B as B on a.xid=b.id and a.xtype= ' B '

Lefe join C as C on a.xid=c.id and a.xtype= ' C '

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

(1) This tagging framework allows you to associate tags (or other attributes) with any other resource in the database.

Just like the design of EAV, you should suspect any design that generates wireless extensibility.

(2) The foreign key cannot be declared in the database China.

(3) There is a column that shows what the other columns of the record are related to.

Any foreign key forces all rows in a table to refer to the same table.

Rational use of anti-patterns : You should try to avoid using polymorphic associations, and you should use foreign KEY constraints to ensure referential integrity.

Because polymorphic associations are often overly dependent on upper-level programming rather than database metadata.

solution : Make Relationships simple

1, reverse reference, polymorphic Association is a reverse association

2. Create a crosstab: Create a separate crosstab table for each parent table, with each crosstab containing a foreign key to the target table and a foreign key to the corresponding schedule.

The crosstab is mostly used to store many-to-many relationships, and if the relationship is one-to-many, you can guarantee a pair of multiple by creating a unique constraint on the cross-table.

Referential integrity can be guaranteed by using crosstab tables.

3. Create a common Super table: Create a base class table and have all of the parent tables inherit the base class table.

SQL anti-Pattern Learning Note 7 Polymorphic Association