SQL Design Anti-pattern

---restore content starts---

1. Crossing the road----> Target: Stores multi-valued attributes.

1) Error method: Use formatted comma to split the list.

1-> is not suitable for querying, locating data, unable to use aggregate functions to group, not conducive to the more specific product account. does not facilitate validation of the specified illegal field.

2-> difficult to select the appropriate delimiter, the length of the list is difficult to limit.

2) Solution:

1-> Create a cross-table.

2-> When a table has a foreign key that points to two other tables, we call it a cross-table.

3-> advantages of using a crosstab: the ability to use indexes to improve the efficiency of join queries and to perform aggregate queries. There is no such disadvantage.

2. Simple tree----> target: Tiered Storage and query example: replies.

1) Scenario 1: adjacency table.

Defect: Unable to complete querying all descendants of one node. Unable to execute aggregate function. Removing a node from a tree can become more complex. Chong to get all ancestors for a given node is expensive.

Advantage: Adding leaf nodes is fairly simple.

2) Scenario 2: Path Enumeration

Defect: Having all the flaws in the previous street.

Advantage: Inserting a node is fairly straightforward. and easy to get child nodes, parent nodes.

3) Scenario 3: Nested sets

4) Scenario 4: Closure list

Implementation: Creates an additional relational table that stores node information for all ancestor-descendant relationships in the tree.

Advantage: Direct acquisition of ancestors, descendant nodes. Inserting a delete node is convenient.

Design Table Query child node query tree Insert Delete referential integrity

Adjacency Table 1 Simple difficulty Simple is

Recursive query 1 simple and simple Simple is

Enumerate the road strength 1 simple simple simple No

Nesting set 1 difficult simple difficult no

Closure Table 2 Simple simple Simple is

Adjacency table is the most convenient design, if database wisdom teeth with or connect by prior recursive query, that use adjacency table query more efficient.

The enumeration path is a straightforward way to show the path between ancestors and descendants.

Nested sets do not guarantee referential integrity.

The closure table is the most versatile design.

3. The ID target----is required to establish the primary key specification.

Primary key: Is the guarantee that the data row is unique to the entire table, it is a logical mechanism to navigate to a record and ensure that it is not duplicated, and the primary key can also be referenced by a foreign key

Establishes a relationship between a table and a table.

MySQL provides an underlying scenario that is unrelated to the current process to ensure that a globally unique integer is generated every time as a primary key. Even if the client is initiating concurrent operations at this time.

Importance of PRIMARY KEY constraints: XX If you do not use a PRIMARY KEY constraint, you must check for duplicate rows.

1) Make sure that the data in a table does not appear duplicate rows.

2) reference a single row of records in the query.

3) Support foreign key.

Note: If both tables have the same column names, you can use the using to make a junction query.

Note: The built-in function in MySQL gets the last value generated by a sequence: last_insert_id ();

When a client initiates a concurrent request to live with the latest record ID, if the current maximum value of +1 is not reliable, the same value may be obtained simultaneously by multiple clients, to avoid the contention that the entire table must be locked.

4. Objective: Simplifying the database architecture

Omitting foreign KEY constraints can make the database design simpler, more flexible, or more efficient, but additional code must be added to maintain referential integrity manually.

Note: Any schema that needs to be locked is poorly behaved in high concurrency and large data volume queries.

When forced to use a database product that does not support foreign key constraints, such as the MySQL MyISAM storage engine, a monitoring script must be used to maintain referential integrity.

Advantages of foreign keys:

1) Do not perform a select check before updating or deleting records.

2) No need to lock the entire table when synchronizing changes.

3) no longer need to perform regular monitoring scripts to fix unavoidable orphaned data.

5. Support variable data.

1) Single-table inheritance.

2) Entity table inheritance.

3) class table inheritance.

4) Semi-structured data model.

　　

6. Polymorphic correlation. Target references multiple parent tables

As much as possible to avoid polymorphic associations, you should use foreign KEY constraints to ensure referential integrity. Polymorphic associations are often overly dependent on the upper program code rather than the metadata of the database.

When you use an object-oriented framework, polymorphic associations seem unavoidable. This type of framework can reduce the risk of using polymorphic associations through good logic encapsulation.

SQL Design Anti-pattern