Database Table Structure Extension Based on purexml Technology

December 21, 2007

At the beginning of the delivery and use of information systems, the database table structure is often designed with clear logic structures and the management users
However, when the information system project is running for a period of time, as the business continues to change and the processing process changes, the information system needs to be improved and modified from the front-end interface to the back-end database, database
The table structure must be extended. We usually use the method of adding backup fields, extending the connotation of fields, adding Master/Slave tables, and managing tables for database expansion. Such expansion of the database table structure often leads to operational
Risks of interruption and operation. By analyzing the shortcomings of common database table structure extension methods, this paper proposes several methods based on purexml
The database table structure extension mode can successfully end the technical difficulties of database expansion.

Overview

Letter
The construction of information systems often follows the Business Research, demand analysis, and then enters the database and software summary design, detailed design, and code writing stages, in this process, database designers often adjust
The results of research and requirement analysis are used to design the database table structure. At this time, the Database Designer considers the database structure as a whole and plans the database structure as a whole. The entire database table structure has clear logic, convenient management, and conforms
3nf
. The information system project has been running for a period of time. As the business continues to change and increase, the processing process is constantly changing. Driven by such business requirements, our software system needs to be constantly modified and improved, this
Modification and improvement are not only the adjustment of the interface and the addition of modules, but also the necessary adjustment and modification of the database table structure.

During maintenance of such projects
For database adjustment and expansion, you must modify the database design document and adjust the data dictionary. Ideally, the documents are complete and complete, but in actual work, because of the responsibility and
The contempt of the document means that every adjustment to the database table structure will lead to confusion in the database table structure, leading to the risk of system management, maintenance and re-modification and adjustment of software for future work. Secondly, database Expansion
And adjustment, the database designers have high requirements, it is easy to cause the potential of the paradigm design in the database design, resulting in a sharp decline in database performance; the most noteworthy is that, because database storage has a large number of services
Each modification and adjustment of database fields must be stopped, resulting in operation interruption and operation risks. Especially for core business systems running online, and a number of 7 × 24
Hours of business systems, each database downtime operation has a huge impact on the operation, and may also lead to adverse social impact.

In order to effectively expand the database, we usually use reserved fields, field connotation extensions, extended slave tables, and joined tables in actual work, this kind of expansion often has several problems:

Limitations caused by reserved standby fields at the beginning of Design

In order to reduce the field adjustment in the database table in the future, some designers reserve some spare fields for several tables that may be extended based on experience at the beginning of the design. The reserved standby field method can increase the scalability to some extent, but there are the following hidden risks:

1. The number of reserved fields cannot be predicted. If n fields are reserved or N + 1 fields are reserved, the expansion uncertainty is caused by unpredictable reserved fields;
2. The reserved field type cannot be predicted. The reserved field type or value cannot be predicted or evaluated;
3. In reserved extension fields, these reserved extension fields often break the most basic paradigm requirements of the database. the most basic requirements of the database paradigm are atomicity and uniqueness, the extended fields are not clearly defined, which is uncertain and non-atomic and non-unique.

The data volume of fields in the table structure is not extended, and the connotation of several fields is extended.

Quantity
Adjustments to databases pose operational risks. Some database designers do not increase the number of fields in the database table structure to meet database storage requirements, rather, it is very "smart" to add the connotation of a field
Line expansion allows some fields to store more than 2 meanings at the same time, and the program analyzes the attributes and content of the values stored in this field. For example, a field is originally defined
Varchar (4). If the value starting with a letter is stored, for example, a001 indicates XX, and the value starting with a value, for example, 1000 indicates xx.
YY; another way is to use the delimiter to expand the field, such as a001 + 1000
. In our database design, every field in the database table is a single attribute, which cannot be further divided and Atomic. This is the first paradigm theory in database design, any database design should comply
Keep the first paradigm. This design not only violates the first paradigm theory of the database design, but also results in the need for the program to "decode" the data to be read before the query, statistics, and so on, making the overall performance of the database greatly
Lower.

Increase the number of slave tables, but the number of slave tables cannot be determined.

When database tables are extended, the slave table must be added to cope with database expansion when exists, the number of slave tables to be added cannot be predicted. Each time a slave table is added, the database is modified and adjusted.

Add Association, but the fields in the joined table cannot be predicted.

Due to changes in the business logic, if the original table t1 and t2 have a n: M relationship, the associated table must be added, but the number of fields in the associated table cannot be predicted, each adjustment to the joined table may affect the database.

Connect
When the database table structure is analyzed by using tables and Field Extensions, we will find that modifying the database often causes a lot of inconvenience, it is worth noting that every time you modify a database, it is often a service.
It needs to be very urgent. Considering the previous steps to ensure the stability and reliability of the previous programs, it is impossible to adjust and restructure the original database.
Only some fields in the database table structure are allowed, which leads to confusion in the database table structure and out-of-control in management and maintenance.

 

Back to Top

Database Table Structure Extension Principle

To reduce the impact on the original system and store historical data, we must adjust the database according to the following three principles:

1. Disable modification and extend. The fields in the original table structure contain data information and cannot be deleted or modified. If you delete these fields, data is often lost, especially for database operations on some associated tables;
2. The key to modifying the table structure is to reduce the impact on operations. Every time a database is adjusted, data needs to be backed up to interrupt the business system. Interrupting the business system will cause huge losses to production and operation and adverse social impact, therefore, database adjustments must take measures to reduce the impact on the production and operation systems;
3. The table structure is rarely restructured, but only adds or decreases fields. The extension of table structure is based on the operation of existing systems. Considering the stable operation of existing systems, we seldom restructure the original table structure, but only add and expand fields and database tables in the table.

 

Back to Top

Technical Features of DB2 V9 purexml

Test
Considering the problems encountered in the tables and Field Extensions and the basic principles of database table structure adjustment, we believe that purexml can help us better solve this problem. DB2 V9
For the first time, the purexml technology provides a Hierarchical Storage method that matches the XML hierarchical structure and the corresponding operation access method.
XML is a new data type. Almost every DB2
Components, tools, and utilities have been enhanced to identify and process this new data type. The new storage mode is retained in the parsed annotation tree format (similar to the XML Document Object Model (DOM ).
XML, which is separated from relational data storage.

Figure 1. New XML relational storage model of DB2

In
The database engine at the top of two types of data storage (relational and XML) can process XQuery, XPath, SQL, and SQL/XML. This engine uses SQL and
The bilingual query compiler of the XQuery parser. Therefore, developers can use SQL or XQuery based on applicable principles.
Any language (or both) that supports transaction-level XML operations.

 

Back to Top

Database Table extension mode based on purexml Technology

To cope with the expansion of the table structure of the database, we can use XML to have the characteristics of self-description and hierarchical rows, which can easily store various types of databases. For the extension of different database table structures, the extension of the field mode, the extension of the slave table mode, and the extension of the master and slave unfamiliar can easily adapt to the adjustment of various types of database table structures.

Field mode Extension

Figure 2. Field Extension Mode

To add fields to the table structure, you only need to expand the XML columns to complete database expansion.

Method
: Multiple fields must be added to the left table. The right table only needs to expand the XML fields;

Advantages
: It is applicable to the expansion of database fields. Due to XML-based fields, there is no limit on the number of fields, and there is no restriction on the field type, and no downtime is required for modification;

Applicability
: Suitable for attributes that only have a unique dependency on the primary key

Expansion of Master/Slave Mode

Figure 3. Master-slave Extended Mode

For the mode that you need to add a slave table to expand the master table, you only need to expand the XML type at the end of the column, and the extended slave table is all stored in XML.

Method
: You need to add multiple foreign keys and slave tables on the left side. You only need to expand the XML fields according to the slave table structure on the right side.

Advantages
: It is applicable to expansion of the slave table. Due to XML-based fields, there is no limit on the number of slave tables, and there is no restriction on Field Types in the slave table.

Applicability
: Suitable for the expansion of the slave table to be added.

Extension of association Mode

Figure 4. Description of the associated extension mode

Method
: Due to the increase of business rules, an association table must be added for the N: M relationship, and an XML field must be added to the association table.

Advantages
: Adding XML fields in the joined table can cope with the addition of fields and the addition of fields from the table (see field mode and master-slave mode), thus forming a complex database extension method.

Adaptation Scope
: Increased the flexibility and scalability of database design.

This article introduces three extension modes of database table structure based on purexml technology. Through this mode, the modification and extension of database systems are very convenient and easy to use, in addition, various modes can be further combined and superimposed to cope with the expansion of more complex database and table structures.

 

Back to Top

Conclusion

To
This paper analyzes several common methods for database expansion, and proposes three Extension Technologies Based on purexml Technology for database models.
Fields enable on-demand distribution, elastic scaling, and unlimited scaling of database extensions. Secondly, XML is based on self-descriptive, and XML
The field method makes the database structure clear and easy to manage and maintain. When the field is added, the system does not need to stop processing, reducing the impact on system operation and operation risks.
The extension mode ensures that the modification of the database table is disabled. For the extension development, software developers only need to follow the XML extension mode idea and perform crud operations on the database.
Operating database operations are encapsulated to greatly improve system software maintenance efficiency and reduce maintenance costs.