Extended data management — how to define extended fields of data table

Time:2020-1-16

How to define extended fields of data table

Because of product upgrade or product project adaptation and other reasons, the data fields of our relational database need to be extended. For example, we used to have a user table, which has ID, username, password fields. Now we need the age, male, address properties of this user. How can we realize the extension based on the original table?

We usually use the following methods to realize data extension: dynamic adding field, JSON format storage, reserved field, attribute field row storage

(1) , dynamically add property fields

That is, when you need to add an attribute. First add a field to the table in the database.

For example, to give the user the attribute of age, add the age field to the user table. If you want to use this field, you need to change the corresponding Java program. Obviously, this method is not conducive to flexible expansion, and it takes a long time to add a field to a large table, which is not suitable for 7 * 24 services.

  • Advantages: it conforms to the habit of data table design, and has no difference from the original field in actual use
  • Disadvantages: every attribute added will change the corresponding Java program, which is not conducive to flexible expansion

(2) , JSON format storage

That is to say, a group of fields to be extended are put into a field, and each field is formed into a large string in the form of JSON.

For example, the user table above can be designed as follows:

field type Remarks Data instance
id int(10) ID 1239512
username varchar(20) User name zhangsan
password varchar(20) Password qwerty
version int(10) Version (version of extended field) 3
extended_filed varchar(200) Extended field {‘age’: ’18’, ‘male’: ‘F’, ‘address’: ‘hefei’}
  • Advantages: insert / update / delete can be operated conveniently; table structure and too much java code need not be changed frequently
  • Disadvantages: the extended field does not support index; the key name is redundant; if you want to query according to the extended field, it is not convenient and efficient, so you need to traverse all the data;

(3) , reserved fields

Is it to define several extension fields for the table first or take the user table as an example.

Define several reserved fields for the user table, so that any data type can be parsed into a string and the encoded data can be saved.

field type Remarks Data instance
id int(10) ID 1239512
username varchar(20) User name zhangsan
password varchar(20) Password qwerty
extended_filed1 varchar(50) Reserved extension field 1 18
extended_filed2 varchar(50) Reserved extension field 2 F
extended_filed3 varchar(50) Reserved extension field 3 hefei
  • Advantages: it can select / insert / delete / update normally; some complex queries can also be implemented
  • Disadvantages: too few may not work, too many may not work, affecting performance; extension fields are public, and can’t be based on the field name as the name implies. You need to maintain the corresponding relationship when enabling, and find the corresponding relationship when using; the number of extension fields can’t be precisely defined.

(4) , property field row store

As the name implies, this method needs to create a new property field table, in which the field name and value of the extended field are maintained

Such as the extended field table of user table

field type Remarks Data instance
id int(10) ID 13214213
user_id int(10) ID property of user table 1239512
filed_name varchar(20) Extension field name age
filed_value varchar(20) Extended field value 18
  • Advantage: the field name can be as the name implies, and the field can also be expanded dynamically
  • Disadvantages: the key name is redundant, so the key name should be as short as possible and can be encoded; two tables need to be operated during select / insert / delete / update, which is slightly complicated

(5) , extended field configuration table and extended field value table

Extended field properties and extended field values of multiple tables. The configuration is stored in one table, and the values are stored in multiple tables separately

Extended field table:

Extended data management -- how to define extended fields of data table

Extended field value table:

Extended data management -- how to define extended fields of data table

  • Advantages: advantages: only need to maintain the configuration table, and reduce the amount of code maintenance
  • Disadvantages: complex operation