Database design five essentials to make database design more standardized

Typically, there are two ways to judge whether a database is designed to be a comparison specification. One is to see if you have a large number of narrow tables, and the second is whether the number of wide tables is enough. If these two conditions are met, it can be explained that the standard level of the database is still relatively high. This, of course, is a two-a-kind indicator. In order to meet the requirements of database design standardization, in general, the following five requirements must be met.

Requirement One: Nullable columns should be avoided in the table.

Although empty columns are allowed in the table, empty fields are a special type of data. When the database is processed, special processing is required. In this case, 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 table is designed to allow empty fields in the table, we should try to avoid them. If it is really necessary, we can deal with these empty fields in a few compromises, so that the impact on database performance is minimized.

One is to avoid the generation of empty fields by setting default values. In the case of a personnel management system, sometimes the ID field may be allowed to be empty. Because not everyone can remember their ID number. And when the employee reports, the ID card may not be brought around. Therefore, the ID number field is often not available in a timely manner. To do this, the ID number field can be allowed to be empty to meet the needs of these special cases. However, in the database design, you can do some processing. If the user does not enter the content, then the default value of this field is set to 0 or N/A. To avoid the creation of empty fields.

The second is that if a table is allowed to be empty, the column is 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, I recommend that you create a separate table to hold the columns. The main table is then associated with the secondary table by the keyword. Storing the data in two separate tables makes the design of the main table simpler and satisfies the need to store empty value information.

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

If there is now 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 more than one person to work together to complete. Therefore, in the Product basic information table product Developer This field, sometimes you may need to fill in the names of multiple developers.

such as invoicing management, but also need to manage the customer's contact person. Sometimes, a company may only know the name of a buyer's customer. However, if necessary, the enterprise needs to manage the customers ' purchasing representatives, warehouse personnel and financial personnel. 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 will result in duplicate values or columns. If we can also design, the customer information, contacts are placed in the same table. To solve problems with multiple contacts, you can set up first contact, first contact phone, second contact, second contact phone, and so on. If you have a third contact, a fourth contact, and so on, you 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 in exchange for six of 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 say, can directly modify the buyer's name AH. 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 the buyer corresponding to a number. In the case where the number is not changed and the name changes, the name of the change is displayed on the purchase order. This is not conducive to the time of the tracking.

Therefore, in the database design should try to avoid this duplicate value or column generation. I suggest that if the database administrators encounter this situation, you can change the policy. If the customer contact person set up a separate table. The Supplier information table is then connected to the customer contact information table through the customer ID. In other words, try to manage the duplicate values in a separate table. These independent tables are then linked by views or other means.

Request three: The records in the table should have a unique identifier.

In the database table design, the database administrator should develop a good habit, with an ID number to uniquely identify the row records, and not by the 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 should preferably have a database for automatic management, rather than the task to the foreground application. Otherwise, it is easy to create a case where the ID value is not uniform.

Also, it is best to add line numbers when designing a database. As in sales order management, the ID number is not maintained by the user. However, line numbers can be maintained by the user. In the line of the sales order, the user can sort the order lines by adjusting the size of the line numbers. In general, the ID column is in the 1-per-unit progression. However, the line number will be 10 units progressive. In this case, the line numbers are extended in 10, 20, and 30 in the normal way. If the user needs to turn the line number 30 record to the first line display. At this point, the user can change the row number if the ID column cannot be changed. If you can change the line number to 1, you can sort by line number when sorting. In this case, a record with a line number of 30 now becomes 1 and can be displayed in the first row. This is an effective complement to the ID column in the actual application design. This content is not in the textbook. This technique needs to be mastered in practical application programming.

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

A more complex application system, the corresponding database table is often in thousands. It would be difficult for a database administrator to see the object name to understand the role of the database object. And when database objects are referenced, the database administrator also has a headache for not being able to quickly find the database objects they need.

To this end, the author established, before the development of the database, it is best to spend a certain amount of time to develop a database object prefix naming specification. As the author in the database design, like the front desk application negotiation, determine a reasonable naming specification. The most common use of the author is to define the background database object prefix name according to the foreground application's module. If the table related to the Material Management module can be prefixed with M, and the order management related, then C can be used 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 a consensus between the database administrator and the foreground application developer, and that the name of the object should be defined in strict accordance with this naming convention.

Second, tables, views, functions and so on are best also have a uniform prefix. The view can be prefixed with V, and the function can be prefixed with F. This allows the database administrator to find the objects they need in the shortest amount of time, both in daily administration and object referencing.

Requirement five: Try to store only 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 described here refers to the object that you need to describe itself. I would like to give an example, it is estimated that everyone can understand the content. If there is now a library system, there are basic information, author information two entity objects. It is also possible for the user to place the two entity object information in the same table. If you can design the table as a book name, book author and so on. However, such a design will bring a lot of trouble to the subsequent maintenance.

When a subsequent book is published, the author's information needs to be added to 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 changes, you also need to change the record of each book. At the same time, if the author's book is removed from the database, the author's information will be lost. 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 separate tables, respectively, the basic information table, the author's basic information table, book and author correspondence table and so on. After this design, all of the above problems have been solved by the edge.

The above five is in the database design to achieve the standard level of the basic requirements. In addition to these there are a lot of detail requirements, such as data types, stored procedures, and so on. Moreover, the database specification often does not have the strict technical restriction, mainly relies on the database administrator daily work experience accumulation.

Database design five essentials to make database design more standardized