Five requirements for normalization of database design

In general, two aspects can be used to determine whether the database design of the comparison specification. One is to see whether there is a large number of narrow tables, and the second is that the number of wide tables is small enough. If these two conditions are met, it can be explained that the standard of the database is still relatively high. Of course, this is a two-generalities indicator. In order to meet the requirements of database design normalization, the following five requirements are generally required.

Requirement One: Nullable columns should be avoided in the table

Although empty columns are allowed in the table, empty fields are a relatively special type of data. When the database is processed, special processing is required. In this way, the complexity of database processing records is increased. When there are more empty fields in the table, the performance of the database processing is much lower under the same conditions.

So, while the database tables are designed to allow empty fields in the table, we should try to avoid them. If necessary, we can deal with these empty fields in a compromise way to minimize the impact on database performance.

One is to avoid the creation of empty fields by setting the default value. As in a personnel management system, sometimes the ID number field may be allowed to be empty. Because not everyone can remember their ID number. And when the staff report, may not be with the identity card around. Therefore, the identity card number field is often not available in time. For this reason, the ID number field can be allowed to be empty to meet the needs of these special situations. However, when designing a database, you can do some processing. If the user does not enter the content, the default value for this field is set to 0 or N/A. To avoid the creation of empty fields.

The second is that if you have a table, the columns that are allowed to be empty are more than one-third of the total number of columns in the table. And, in most cases, these columns are optional. If the database administrator encounters this situation, the author recommends that a separate table be created to hold the columns. The main table is then associated with the secondary table by keyword. Storing the data in two separate tables makes the primary table simpler to design and also satisfies the need to store null information.

Requirement two: The table should not have duplicate values or columns

If there is a invoicing management system, this system has a product basic information table. This product development can sometimes be done by one person, and sometimes it takes a lot of personal cooperation to get it done. Therefore, in the Product base Information table product Developer This field, sometimes you may need to fill in the name of multiple developers.

such as invoicing management, but also need to the customer's contact person to manage. Sometimes, an enterprise may only know the name of a customer buyer. However, if necessary, the enterprise needs to the customer's purchasing representatives, warehouse personnel, financial staff to jointly manage. Because on the order, you may need to fill in the name of the purchasing representative, but on the shipping list, you need to fill in the name of the warehouse manager and so on.

In order to solve this problem, there are many ways to implement it. However, if the design is unreasonable, it can result in duplicate values or columns. such as we can also design, the customer information, contacts are placed in the same table. To resolve problems with multiple contacts, you can set up the first contact, the first contact phone, the second contact, the second contact phone, and so on. If you have a third contact, a fourth contact, and so on, you will often need to add more fields.

However, this design will produce a series of problems. If the customer's buyer liquidity is relatively large, in a year changed six buyers. At this point, how to manage in the system? Do you want to create six contact fields? This will not only result in an increase in empty fields, but also frequent changes to the database table structure. Obviously, it is unreasonable to do so. Some people also say that you can directly modify the name of the buyer. However, it will change the name of the purchaser on the original purchase order. Because the customer buyer information in the purchase order is stored in the database is not the buyer's name, but only a buyer's corresponding number. In cases where the number does not change and the name changes, the name that is changed is displayed on the purchase order. It's not good for a time tracking.

Therefore, in the database design, try to avoid this duplicate value or the production of columns. I suggest that if the database administrator encounters this situation, you can change the strategy. If you set up a separate table for the customer contact person. The Supplier information table is then connected to the customer contact information table through the customer ID. That is, try to place the duplicate values in a separate table for management. Then connect these separate tables by view or by other means.

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

In database table design, database administrators should develop a good habit, with an ID number to uniquely identify the line record, rather than by name, number and other fields to distinguish the record. Each table should have an ID column, and no two records can share the same ID value. In addition, this ID value is best to have the database for automatic management, and do not put this task to the foreground application. Otherwise, it is easy to create a situation where the ID value is not uniform.

In addition, in the database design, it is best to add line number. In the case of sales order management, the ID number is not maintained by the user. However, line number users can be maintained. As in the line of sales orders, the user can sort the order lines by adjusting the size of the line number. Typically, the ID column is in the 1-per-unit progression. However, the line number will be progressive in 10 units. So, normally, line numbers are expanded in 10, 20, and 30. If at this time the user needs to line number 30 records to the first line display. At this point, the user can change the row number to be implemented without changing the ID column. If you can change the line number to 1, you can sort by line number. In this case, the record of the original line number 30 is now 1 and can be displayed in the first row. This is an effective supplement to the ID column in the practical application design. This content is not in the textbook. This technique needs to be mastered in the actual application design.