Five requirements for database design standardization

Requirement 1: avoid empty columns in the table. Although empty columns are allowed in the table, empty fields are special data types. Special processing is required during processing. In this case, the complexity of the database processing records will be increased. When there are many blank fields in the table, the database processing performance will be much lower under the same conditions. Therefore, although empty fields are allowed in the database table design, we should avoid them as much as possible. If necessary, we can take some discounts to process these empty fields.

Requirement 1: avoid empty columns in the table.

Although empty columns are allowed in the table, empty fields are special data types. Special processing is required during processing. In this case, the complexity of the database processing records will be increased. When there are many blank fields in the table, the database processing performance will be much lower under the same conditions.

Therefore, although empty fields are allowed in the database table design, we should avoid them as much as possible. If necessary, we can take some discounts to process these empty fields, minimizing their impact on database performance.

First, you can set the default value to avoid null fields. For example, in a personal affairs management system, sometimes the ID card number field may be blank. Because not everyone can remember their ID card numbers. However, when an employee reports, his/her ID card may be missing. Therefore, the ID card number field is often not provided in a timely manner. Therefore, the ID card number field can be empty to meet the needs of these special circumstances. However, some processing can be done during database design. If no content is entered, set the default value of this field to 0 or N/. To avoid null fields.

Second, if a table contains many columns that are allowed to be empty, it is close to 1/3 of the total number of columns in the table. In most cases, these columns are dispensable. If the database administrator encounters this situation, I suggest creating another sub-table to save these columns. Then, the master table is associated with the sub-table by using the keyword. Storing data in two independent tables makes the design of the winner table easier and meets the needs of storing null values.

　　Requirement 2: duplicate values or columns should not exist in the table.

For example, there is an inventory management system, which contains a basic product information table. Sometimes this product can be developed by one person, and sometimes it can be completed by multiple people. Therefore, in the product basic information table, you may need to enter the names of multiple developers.

For example, you also need to manage the customer's contacts in the inventory management. Sometimes, the company may only know the name of one buyer of the customer. However, when necessary, the enterprise must manage the customer's procurement representatives, warehouse personnel, and financial personnel. Because in the order, you may need to fill in the name of the purchase representative; but in the delivery order, you need to fill in the name of the warehouse management personnel and so on.

There are multiple implementation methods to solve this problem. However, if the design is unreasonable, duplicate values or columns may occur. For example, we can design this way to put customer information and contacts in the same table. To solve the problem of multiple contacts, you can set the first contact, the first contact phone number, the second contact, and the second contact phone number. If there are third and fourth contacts, more fields are required.

However, this design will lead to a series of problems. If the customer's purchasers are more mobile, six purchasers will be changed within one year. How can I manage it in the system? Should we create six contact fields? This will not only increase the number of blank fields, but also require frequent changes to the database table structure. Obviously, this is unreasonable. Some people say that you can directly modify the name of the buyer. However, the name of the buyer in the original purchase order will be changed. This is because the customer buyer information stored in the database is not the name of the buyer, but the number corresponding to the buyer. If the number is not changed but the name is changed, the changed name is displayed on the purchase order. This is not conducive to tracking.

Therefore, when designing a database, we should try to avoid repeated values or columns. I suggest you change the policy if the database administrator encounters this situation. For example, set another table for the customer contact. Then, the supplier information table is connected to the customer contact information table by the customer ID. That is to say, try to store duplicate values in an independent table for management. Then, the independent tables are connected by views or other means.

　Requirement 3: The record in the table should have a unique identifier.

When designing a database table, the database administrator should develop a good habit of using an ID number to uniquely identify a row record, rather than distinguishing records by names, numbers, and other fields. Each table should have an ID column, and neither of the two records can share the same ID value. In addition, it is best to have a database for automatic management of this ID value, instead of assigning this task to the foreground application. Otherwise, the ID values may be inconsistent.

In addition, it is best to add row numbers when designing databases. For example, in sales order management, the ID number cannot be maintained by the user. However, you can maintain the row number. For example, in the sales order line, you can sort the order line by adjusting the size of the row number. Generally, the ID column is progressive in units of 1. However, the row number must be progressive in the unit of 10. In this case, the row numbers are expanded in order of 10, 20, and 30. If you want to adjust the record with the row number 30 to the first line. In this case, you can change the row number if you cannot change the ID column. For example, you can change the row number to 1, and sort by the row number during sorting. In this case, the record whose original row number is 30 is now changed to 1 and can be displayed in the first row. This is an effective supplement to the ID column in the actual application design. This content does not exist in textbooks. This technique can be mastered only in actual application design.

　Requirement 4: The database object must have a uniform prefix name.

A complicated application system usually has thousands of database tables. It may be difficult for the database administrator to see the object name and understand the role of the database object. In addition, when the database object is referenced, the database administrator also has a headache for failing to quickly find the desired database object.

To this end, I have established that it is best to spend some time developing a database object prefix naming convention before developing the database. For example, when designing databases, I like to negotiate with the front-end application to determine reasonable naming rules. What I usually use most is to define the background database object prefix name based on the module of the foreground application. For example, tables related to the material management module can use M as the prefix, while orders related tables can use C as the prefix. The specific prefix can be defined based on your interests. However, it should be noted that this naming convention should reach a consensus between the database administrator and the foreground application developers, and define the object name strictly in accordance with this naming convention.

Second, tables, views, and functions should also have a uniform prefix. For example, a view can use V as the prefix, while a function can use F as the prefix. In this way, the database administrator can find the desired object in the shortest time in both daily management and object reference.

　　Requirement 5: store data of a single object type as much as possible.

The object type is not the same as the data type. The entity type here refers to the object to be described. Let me give an example to illustrate the content. For example, there is now a library system with two entity objects: basic information of books and author information. If you want to put the two object information in the same table, you can also. For example, you can design a table as a book name or author. However, such design will cause a lot of trouble for subsequent maintenance.

For example, when a book is published in the future, the author information needs to be added for each published book. This will undoubtedly increase the additional storage space and the length of the record. In addition, if the author's situation changes, if the address is changed, you need to change the records of each book. At the same time, if the author's books are all deleted from the database, the author's information will disappear. Obviously, this does not meet the needs of standardized database design.

In this case, I suggest you break down the table above into three independent tables, including the basic information table of books, the basic information table of authors, and the corresponding table of books and authors. After this design, all the problems mentioned above will be solved.

The above five are the basic requirements for Standardization in database design. In addition to these requirements, there are also many detailed requirements, such as data types and stored procedures. In addition, database specifications often have no technical restrictions, mainly relying on the accumulation of daily work experience of database administrators.