Talk about the design of database cascade deletion and pseudo-deletion

Background:

These two days to review the next design patterns and data structure, and fill the basic knowledge, and then insomnia all night, I do not know why the idea of cascading and pseudo-deletion of data this problem.

Because cascade deletion is almost everyone will encounter problems, but the scheme is limited but not good, so welcome everyone to gather think Wen Yi, the following content welcome to discuss together.

Cascading deletions: Mode 1: Database setting Cascade:

Regular MSSQL, MYSQL, and Oracle provide cascading deletions for tables that have a primary foreign key relationship set.

Advantages: Accurate data, easy to use, database design at the beginning of the set.

Disadvantages:

1: Increase the additional cost of foreign key detection when adding or deleting.

2: Potential danger is Sueda (e.g. deleting a department or role, discovering a cascade of recursion, and the entire system's data is gone).

3: Inconvenient to trigger other events.

4: Developers may be masked in detail.

Overall Description: Suitable for small systems, small parts, no cache state of the situation use.

General summary: rarely used.

Mode 2: Trigger processing.

Pros: DBA likes.

Cons: Programmers don't like it, it's easy to be B.

Overall Description: Suitable for the system leader of the DBA Hobby scenario, and the business without caching scenarios.

Overall summary: Internal business system use more, external system use less.

Mode 3: Business code Control

Pros: Programmers like, freedom of control is large.

Cons: Programmers like, free control is large (as the business expands, need to fill the code everywhere).

General Description: Love Freedom, love life, love to write code.

Overall summary: The conventional approach is widely used in all systems.

think on the basis of Mode 3: How to unify the architecture design to reduce the distribution of code?

Let's talk about the more complex pseudo-deletion issues:

Trigger deletion and pseudo-deletion 1: Trigger mode

Advantages:

1: Delete by trigger and move old data to another library or table.

2: The data is clean, the table pressure is small.

3: Code business logic simplifies.

4:dba likes it.

5: A developer also likes it.

Disadvantages:

1: Bad control triggers other external business or events (such as the deletion of documents at the same time, but the method is always more difficult).

2: Overall database pressure (this also depends on the business situation).

3: Cascade cache is not well controlled (and the synchronization of write triggers is clear that the business can still be controlled).

4: Two people who took over the maintenance didn't like it.

Overall Description: The overall shortcomings are not obvious, late maintenance inconvenience.

Overall Summary: The business system used relatively more.

2: Pseudo-Deletion method

Advantages:

1: Data is only identity state, data recovery is easy.

2: Developers like it.

Disadvantages:

1: You need to add a version number or isdeleted in each table of the system.

2: Business queries are required to increase the filtering conditions.

3: Need to cascade update the identity symbol.

4: Dirty data exists.

5: The cache requires full control.

Overall Description: The advantages are not obvious, the disadvantage is that the business code distribution is complex.

Overall Summary: The overall use is not much.

Extended content:

1: Last night did not intend to sweep the auspicious day an article 2010 wrote article, The effect is:

Spend one weeks adding pseudo-censored deletemark fields and changing all business code. (Comments on the main bias trigger scheme, and to personal attacks, 6 years later, I believe those people should be able to look at the problem now, the address is not affixed.) ）

2: For the problem of adding fields, some people say view processing.

3: See Another interesting scenario: the problem of adding the same data after a pseudo-delete.

After adding the IsDeleted field, set the original "unique key +isdeleted" to establish the federated primary key.

After deletion: Cyq 0.

New additions: Cyq 1.

Found this time can not be deleted, and then deleted on the two Cyq 0 conflict, How will you solve ?

There is not much content on the Internet to search for pseudo-deletion, and it is possible to foresee that the use of the scheme is not widespread, possibly because of a scenario where there is no unified approach to architecture.

thinking: How to deal with the architecture design and reduce the business code? What is the cascade reaction of the blog park?

Suppose Blog Park to delete or disable a user, how many things does the analysis need to deal with?

1: Almost all tables in the system are related to processing (articles, comments, likes, credits, Flash, recruitment, blogs, knowledge Base, collections, news, etc...) ）

PS: Files, pictures (considering the number of files or pictures outside the station has a lot of references, not processed. ）

2: If the cache needs to be invalidated at all times (this almost causes the whole station cache to fail momentarily, the system will collapse ...) Fortunately, the garden is currently cached without time-sensitive requirements. ）

So here's the question:

1: The garden is fully disposed of, or only partially treated?

Full processing: The workload is a bit large, the code distribution is a bit scattered, as the business increases, but also to add logic code.

Do not handle: Leave the user links around the cause of 404, will not affect the SEO it?

2: What is the user's acceptance of the question on Bo? Delete it? Or is it not to be deleted?

3: Is the garden currently being deleted by the real or the pseudo-deletion?

Summarize:

1: Before all their own quiet thinking, the function in the V5 framework to realize the sharing.

2: Now, share the problem, after discussion, then determine the overall idea.

3: What are the options for the projects you have been involved in? Is there room for improvement in the program?

Talk about the design of database cascade deletion and pseudo-deletion