Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the requirements for database design standardization". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "What are the requirements for standardization of database design"!

Requirement 1: Nullable columns should be avoided in tables.

Although empty columns are allowed in tables, empty fields are a special type of data. When the database is processed, special processing is required. This increases the complexity of database processing records. When there are more empty fields in the table, the performance of database processing will be reduced a lot under the same conditions.

Therefore, although database tables are designed to allow empty fields in the table, we should try to avoid them. If necessary, there are trade-offs to deal with these empty fields that minimize the impact on database performance.

One is to avoid the generation of empty fields by setting default values. For example, in a personnel management system, sometimes the ID number field may be allowed to be blank. Not everyone can remember their ID number. When employees report for duty, they may not have their ID cards with them. Therefore, the ID card number field is often not provided in time. For this reason, the ID number field can be allowed to be blank to meet the needs of these special circumstances. However, in the database design, you can do some processing. For example, when the user has no input content, the default value of this field is set to 0 or N/A. To avoid the creation of empty fields.

Second, if a table, allow more empty columns, close to one-third of the total number of columns in the table. Moreover, in most cases, these lists are optional. If the database administrator encounters this situation, the author recommends creating a separate secondary table to hold these columns. Then associate the primary table with this secondary table by keywords. Storing the data in two separate tables makes the design of the main table simpler, while also satisfying the need to store null information.

Requirement 2: Tables should not have duplicate values or columns.

If there is now an inventory management system, this system has a basic product information table. This product development can sometimes be done by one person, and sometimes it requires multiple people to work together. Therefore, in the product developer field of the product basic information table, sometimes it may be necessary to fill in the names of multiple developers.

Such as inventory management, but also the need for customer contact management. Sometimes, businesses may only know the name of one buyer for a customer. However, when necessary, the enterprise needs to manage the customer's purchasing representative, warehouse personnel and financial personnel together. Because on the order, you may need to fill in the name of the purchasing representative; but on the delivery list, you need to fill in the name of the warehouse manager, etc.

To solve this problem, there are many ways to achieve it. However, if the design is not reasonable, it will lead to duplicate values or columns. For example, we can also design this way, putting customer information and contacts into the same table. In order to solve the problem of multiple contacts, a first contact, a first contact phone number, a second contact, a second contact phone number, etc. may be set. If there is a third contact, a fourth contact, etc., more fields are often added.

However, if it is designed this way, it will create a series of problems. For example, the customer's buyer is relatively mobile, and six buyers have been replaced within one year. How do you manage this in the system? Do you want to create six contact fields? This not only results in an increase in empty fields, but also requires frequent changes to the database table structure. Obviously, that's not reasonable. Some people say that you can directly modify the name of the buyer. However, if this is done, the name of the buyer on the original purchase order will also be changed. Because the customer buyer information on the purchase order is not stored in the database, but only a number corresponding to the buyer. In the case where the name is changed without changing the number, the changed name appears on the purchase order. This is not good for tracking time.

Therefore, in the database design time to try to avoid this kind of duplicate values or columns. The author suggests that if the database administrator encounters this situation, he can change his strategy. For example, set up a separate list of customer contacts. The supplier information table is then linked to the customer contact information table by the customer ID. That is, try to keep duplicate values in a separate table for management. These separate tables are then linked by views or other means.

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

When designing database tables, database administrators should develop a good habit of uniquely identifying row records with an ID number, rather than distinguishing records by fields such as name and number. Each table should have an ID column, and no two records can share the same ID value. In addition, it is better to have a database for automatic management of this ID value, rather than giving this task to the foreground application. Otherwise, it is easy to produce ID values that are not uniform.

In addition, in the database design, it is best to be able to add line numbers. For example, in sales order management, ID numbers are those that users cannot maintain. However, line numbers can be maintained by the user. For example, in the lines of a sales order, the user can sort the order lines by resizing the line numbers. Normally, the ID column is incremented by 1. However, the line numbers should be graduated in units of 10. Thus, under normal circumstances, the line numbers expand in the order of 10, 20, and 30. If the user needs to adjust the number 30 record to the first line display. In this case, the user can change the row number to achieve this if he cannot change the ID column. If you can change the row number to 1, you can sort by row number when sorting. In this case, the record originally numbered 30 is now numbered 1 and can be displayed in the first line. This is a useful complement to the ID column in practical application design. This content is not in the textbooks. You need to master this skill in actual application design.

Requirement 4: Database objects should have uniform prefix names.

A more complex application system, the corresponding database tables are often thousands. It may be difficult for a database administrator to see the object name and understand what the database object does. Also, database administrators have trouble finding the database objects they need quickly when database objects are referenced.

For this reason, the author established that before developing a database, it is best to spend a certain amount of time to develop a prefix naming convention for database objects. Such as the author in the database design, like to negotiate with the foreground application to determine a reasonable naming convention. The author most commonly uses is to define the prefix name of the background database object according to the module of the foreground application. For example, tables related to the material management module can be prefixed with M, while tables related to order management can be prefixed with C. What prefix to use can be defined in terms of user preferences. However, it is important to note that this naming convention should be agreed upon between database administrators and front-end application developers, and object names should be defined strictly according to this naming convention.

Second, tables, views, functions, etc., should also have uniform prefixes. For example, views can be prefixed with V, while functions can be prefixed with F. In this way, database administrators can find the objects they need in the shortest time, whether in daily management or object reference.

Requirement 5: Store only data of a single entity type as far as possible.

The entity type here is not the same as the data type, so pay attention to distinguishing between them. The entity type mentioned here refers to the object itself that needs to be described. Let me give you an example, and I think everyone can understand the content. For example, there is now a library system with two entity objects: basic book information and author information. If the user wants to put these two entity object information in the same table is also possible. Such as the table can be designed as book name, book author, and so on. However, such a design will bring a lot of trouble to subsequent maintenance.

When subsequent books are published, it is necessary to add author information for each published book, which will undoubtedly increase additional storage space and increase the length of the record. Moreover, if the author's situation changes, such as after the change of address, it is necessary to change the record of each book. At the same time, if the author's books are deleted from the database, the author's information will disappear. Obviously, this does not meet the need for database design standardization.

At this point, I believe that everyone has a deeper understanding of "what are the requirements for database design standardization", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.