Policies and management of SQL redundant Fields

The use of redundant fields is a good choice when multi-table joint query is a table with a large amount of data, effectively reducing Io operations. However, considering existing project products, redundant fields are indeed a double-edged sword. Especially for the development of large projects, if you ignore the update of redundant fields in a table, the consequence is catastrophic. How to effectively manage redundant fields is a problem that must be solved in the development team. My solution is to use a dedicated table to manage redundant fields. For example, the article table has the following redundant fields:

Fromusername, tousername

 

How to manage these two fields? Create a table with the following structure:
ID, objtable, objname, sourcetable, sourceid, level, isupdate

Objtable = target table, objname = target field, sourcetable = source table, sourceid = source table ID, level = need to be updated immediately, isupdate = updated

 

Among them, the level field is necessary. Some redundant fields do not need to be updated immediately after modification in the source table. Therefore, you can use a regular update policy to update them.

Using database and table management and a reasonable stored procedure, redundant fields are no longer difficult to use.

For example, if the preceding two fields change, use a trigger or call this stored procedure to check whether there are redundant fields that need to be updated immediately. If this parameter is not required, set isupdate to 0, wait until the periodic policy is updated and isupdate = 1.

 

If memcache is used in the project, you can use another method to further reduce the database I/O operations. Next, expand it.