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.

　Request 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.

　requirement Four: The database object should have a uniform prefix name.

A more complex application system, its corresponding database tables are often in thousands of meters. It would be more difficult to get the database administrator to see the object name and understand the role of the database object. And when database objects are referenced, database administrators can also get headaches for not finding the database objects they need quickly.

To this end, I suggest that before developing the database, it is best to spend a certain amount of time to develop a database object prefix naming conventions. If the author in the database design, like the front of the application negotiation, to determine a reasonable naming norms. The author's most commonly used is to define the background database object prefix name according to the foreground application module. If the table related to the Material Management module can be prefixed with M, and the order management related, you can use C as a prefix. The specific prefix can be defined as a user's hobby. However, it should be noted that this naming convention should be agreed between the database administrator and the foreground application developer, and that the object name is defined strictly according to the naming convention.

Second, tables, views, functions, and so the best also have a unified prefix. For example, a view can be prefixed with V, and a function can use f as a prefix. So database administrators can find the objects they need in the shortest amount of time, whether in day-to-day management or object references.

　　requirements Five: try to store only the data of a single entity type.

The entity type here is not the same as the data type, so be aware of the distinction. The entity type mentioned here refers to the object itself that needs to be described. I would like to cite an example, it is estimated that we can understand the content. If there is a library system, there are two entity objects of book basic information and author's information. It is also possible for the user to put the two entity object information in the same table. If you can design the table as a book name, book author and so on. But such design, will give follow-up maintenance brought a lot of trouble.

If the following books are published, it is necessary to add author information for each published book, which will undoubtedly add additional storage space and increase the length of the record. And if the author's situation changes, if the address has changed, you will need to change the record of each book. At the same time, if the author's books were removed from the database, the author's message would be gone. Obviously, this does not conform to the requirements of database design normalization.

In this case, the author suggested that the above table can be decomposed into three kinds of independent tables, respectively, for the basic information table, the author basic information table, books and author corresponding tables and so on. After this design, all the problems encountered above have been solved by the blade.

The above five is in the database design to achieve the standard level of the basic requirements. In addition to these there are many details of the requirements, such as data types, stored procedures and so on. Furthermore, database specifications often have no strict technical limitations, relying mainly on the accumulation of day-to-day experience of database administrators.