Database dynamic extension fields

Recently in charge of a platform, need to interface with different systems, need to use the dynamic extension of the database field.

Now the more popular extension field method, there are probably the following, ① reserved a lot of columns to expand, ② use version number way to expand, ③ use the form of Key+value to expand. Let me tell you how I think about these methods.

Probably a lot of people will ask, you want to add a field, that does not directly in the database of the corresponding table added on the knot, which has what extension of the design ah, which day to add the field added Bai, can be empty is empty, can not give a default value is done. It is really so simple to add a field if it is allowed for nullable types is really a bit of a problem. If you add a field need not be allowed to empty, and then in the 1KW or so in the data scale added, it may take a long time, of course, these operations can be added in the dead of night (after adding, change the code this can be done)

(i) reserving some fields for tables that may need to be expanded

The database reserves some fields, just in case, to prevent it, wait until needed, you do not need to add new fields in the table, and in so doing, the data in a table should be stored in the adjacent physical space. In fact, this problem is not really solve our needs, because the characteristics of the reserved field is col1,col2 this name, is not very good maintenance, and so on after a few years to see if this is reasonable.

The above is the "over-design", what we should do is to design on demand.

(ii) Use Version number + universal columns in a way to extend

Similar to this 0, if there is a field change, the use of a universal column for storage, but this structure is particularly bad, but the SQL Server2016 has been supported in JSON format, it is also slowly become feasible.

(iii) expansion using the Key+value approach

The following is the most commonly used key+value way to dynamically expand.

Take the most recent business system to cite examples, for different users

The following emphasis is on our approach to this, the initial practice is a user base table (UserBase), and then the corresponding tables are designed for each system. For example, the corresponding business system A (AEX), Bex is similar to this. Because for our entire platform, each business system, the properties of interest are different, and are uncertain, each business system has its own business properties. Based on these conditions, we have designed the AEX into an extensible table Form (AEX (primary key Id,userid,key,value)). At first we design, write code, you will find that we are too dependent on this key value, need to use the key value corresponding to the value of the key value corresponding to the content to compare, and the key value can not be too long, too long will give us a burden. So we have an improved version that is to get rid of this dependency.

The most common way to get rid of dependencies, in object-oriented design, is to introduce a third party, yes, we need to introduce a special configuration table to manage these key values, called Keyconfig table (Id, KeyName) AEX (primary key Id,userid,keyid,value). Then the corresponding value if there are multiple values, you can also set a valueconfig table to store. These changes need to be based on business trade-offs.

Database dynamic extension fields