The design explanation about the classification of the customer basic data storage information in the database

Design | data | Database design description of storage information classification in database ZYX ZYX 2 112 2002-11-14t03:56:00z 2002-11-20t01:46:00z 2002-11-20t01:46:00z 3 259 1480 ufosft Nanjing 12 2 1817 9.2812 7.8 lb 0 2
The design explanation about the classification of the customer basic data storage information in the database



In the design of the system database, often use the classification of customer basic information, such as the classification of business customers, the general designers can use the following methods to achieve:





1. Directly in the process of coding the customer, the customer's classification information is written into the customer code;

Using the classified code information maintained by the system for code writing, the specific code bits of the code have specific classification information, such as: 11 01 00 01 001 Customer Business Type Customer Area code customer ....  ..... Customer Serial Number



This way the table structure can be designed like this:




Customer Profile Table

Customer Code

Customer Name

.......



Code Maintenance Table

Code category

Parent Code

Code

Meaning

......




















The benefits of using this method are: A. Understand the customer's situation through the code

The definition of table structure in B database is relatively simple

C. Customer statistical analysis directly to the customer code processing, the process of simple and good control

Disadvantages of using this method are: A. When users encode customers, they must have an accurate understanding of the customer's detailed classification information

B. Changing client code is difficult when the nature of the customer changes

C. Customer code is fixed, but cannot be modified once defined and used by other business modules

D. Unable to modify the customer classification rules, can not increase the deletion of classification information, the general end users of their customers classification method based on their specific size of the business customer classification design, small enterprises and large enterprise customers will not use the same classification method, so the system flexibility can be the worst





2. In the customer base table to increase the category field, customer code using a simple sequence coding method to achieve;

This design method is the most commonly used database design method in the current application system, customer code has lost the specific meaning, only in the establishment of customer profiles of the system generated by the serial number, there is no other meaning; Of course, you can keep less generic information in the customer number, but the less the better. Define some fields in the table that store classified information, such as: customer-owned region code, customer classification code, customer size code, customer credit category, and so on. In the design of the system can be as far as possible to take into account the user may use all the classification, the user ultimately use not to use by the user according to their own specific circumstances of the processing, you can select some of the fields.



This way the table structure can be designed like this:


Customer Profile Table

Customer sequence Encoding

Customer Name

Regional classification

Scale classification

Credit classification

Enterprise Nature Classification

.......





Code Maintenance Table

Code category

Parent Code

Code

Meaning

Code Maintenance Table

Code category

Parent Code

Code

Meaning




























The advantages of using this method are as follows: A. Customer code definition is relatively simple, do not need the customer basic data entry personnel to the customer specific classification of special understanding, you can keep the classification code default and approximate classification

B. System flexibility is high

C. The impact on the system is relatively small when the customer changes in nature

Disadvantages of using this method are: a. Table design is more difficult, designers have to consider the more comprehensive, all kinds of classification will be through the corresponding classification field to achieve

B. In the system detailed design and code implementation phase of the workload is relatively large, because to consider the user may use those types of classification, and the different classification to do the corresponding code implementation, the code is less universal

C. In the unknown future, if there is an unspecified classification, the system needs to maintain

D. The association between tables and tables (References) is relatively tight, because the customer base table access probability is relatively high, too many associations will cause the system to run speed, it is easy to create the inter-table interlock phenomenon







3). Using the Classification code table

In this design structure, the classification information in the customer's basic information and unclassified information processing, can be classified through the classification of information through a special classification code table, in the customer base table only a classification code table in the index number (code table in the primary key). In the Classification code table, change the original each divided the vertical way that occupies a field for each category occupies a record of the horizontal way, can be different data targeted specific classification methods.

Customer base table, customer code uses the second method of customer code coding scheme, the user needs to classify the customer, you can enter the classification archiving module for processing, the basic data maintenance and classification of information maintenance step-by-step processing.



This way the table structure can be designed like this:




Customer Information table

Customer Code

Customer Name

Classification code

......

Classification code table

Category Code (PK)

Category ordinal (PK)

Specific category Code categories

Specific classification code

1

N

Code Maintenance Table

Code category

Parent Code

Code

Meaning




























The advantages of using this method are as follows: A. Customer code definition is relatively simple, do not need the customer basic data entry personnel to the customer specific classification of special understanding, you can keep the classification code default and approximate classification

B. High flexibility of the system to maintain customer classification code information at any time

C. The impact on the system is very small when the customer changes in nature

D. The system to access the customer data table according to whether the need to query the classification information and free to set whether the association classification code Table

E. All classified archival information in the system can be managed uniformly

Disadvantages of using this method are: A. The workload of the system database design is larger

B. The maintenance of the data query process is more complex, because it involves more than one table of associations, so the efficiency of execution may be reduced





A third approach can be used when designing a multi-tier architecture for a system. The front-end performance layer is only responsible for interacting with the user interface, and the business logic processing is handled in the intermediate business logic layer, and the data storage layer is responsible for the data storage work.