Database Extensible Design Scheme

Field extension scheme for database tables

Traditional Solutions
I. Reserved fields
A reserved field is a pre-set field for subsequent business expansions at the beginning of the database table design, for example
At the beginning of the design, the user table is uid,name,col1,col2,col3. You can try the reservation field directly when you need to extend the field.
Advantages
1. New No lock table required after business expansion
2. Avoid the ALTER TABLE User add command, which causes a long lock table when there is a lot of data in the table.
Disadvantages:
1. Reserving empty fields is a waste of space (although it can be ignored).
2. The reservation field is often not readable, although you can use the ALTER TABLE user rename column to rewrite the column name, but the same will cause the lock table, affecting the TPS.
3. Use the ALTER TABLE User add command to add a field when the reserved field is exhausted or the data type does not match the new field.

Two. Create a new table to join
The performance issues associated with high-data-large-concurrency joins can seriously affect TPS, and some of the less-well-tuned databases encounter joins that are prone to slow changes. Doing a view is equivalent to doing a join

A scheme to increase the inverse normalization of data redundancy
I. Using version numbers and common fields
That is, at the beginning of the design, the user table is users (uid,name,version,content)
When the system is just online (v0 version) the data is
1 sheets 30 {passwd:123}
2 Li 40 {passwd:456}
The V1 version adds the Age,sex field after the data is
1 sheets 30 {passwd:123}
2 Li 40 {passwd:456}
3 Wang 51 {passwd:789,sex:1,age:10}
The old version of the data can be updated by writing an operations program, so that no lock table is required to add fields.
Advantages
New field without lock table, data can be differentiated version, old data upgrade simple
Disadvantages
1. The data in the Content field cannot be indexed, but some databases support JSON retrieval
2. There is a large amount of redundancy in the real field within the Content field, and 1000 data will be stored in 1000 redundant "passwd", "Sex" and "age"

Second, data expansion through the line
The structure of the user table becomes this way user (Uid,key,value)
When the system is online, the data is
1 Name Zhang San
1 passwd 123
2 name John Doe
2 passwd 123
After the system revision, the data becomes
1 Name Zhang San
1 passwd 123
2 name John Doe
2 passwd 123
3 name Harry
3 passwd 123
3 Sex 1
3 age 18

Advantages:
1. Dynamic expansion does not require a lock table
2. You can create an index for each attribute (actually indexing the UID is enough)
3. Old data can be updated by writing an OPS program

Disadvantages
1. There are many database records, and each additional attribute will grow linearly
2. Large amount of redundant data (key field)

Database Extensible Design Scheme