If these two conditions are met, it can be seen that the standardization level of this database is still relatively high. Of course these are two general indicators. In order to meet the requirements of standardized database design, generally speaking, the following five requirements need to be met.
Requirement 1: Empty columns should be avoided in the table.
Although empty columns are allowed in the table, empty fields are a relatively special data type. When processing the database, special processing is required. In this way, the complexity of the database processing records will be increased. When there are more empty fields in the table, the performance of database processing will be much lower under the same conditions.
Therefore, although empty fields are allowed in the table when designing database tables, we should try to avoid them. If you really need it, we can handle these empty fields in some compromises to minimize their impact on database performance.
First, by setting the default value form, avoiding the generation of empty fields. For example, in a personnel management system, sometimes the ID number field may be allowed to be empty. Because not everyone can remember their ID number. When the employee reports, he may not have his ID card with him. Therefore, the ID number field is often not available in time. To do this, the ID number field can be allowed to be empty to meet the needs of these special circumstances. However, when designing a database, some processing can be done. If the user has not entered content, the default value of this field is set to 0 or N/A. To avoid the generation of empty fields.
Second, if there are many empty columns in a table, which are close to one-third of the total number of columns in the table. Moreover, these lists are optional in most cases. If the database administrator encounters this situation, the author recommends creating another subtable to save these columns. Then, the main table is associated with this secondary table through keywords. Storing data in two separate tables makes the design of the main table simpler, and can also meet the needs of storing null value information.
Requirement 2: The table should not have duplicate values or columns.
For example, there is now a purchase and sales management system, and there is a product basic information table in this system. This product development can sometimes be done by one person, and sometimes it requires cooperation from multiple people to complete. Therefore, in the product developer field of the product basic information table, sometimes multiple developers' names may need to be filled in.
For example, in the management of inventory and inventory, the customer's contacts also need to be managed. Sometimes, a company may only know the name of a customer and buyer. However, when necessary, the company needs to jointly manage the customer's 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 shipment form, you may need to fill in the name of the warehouse manager, etc.
To solve this problem, there are multiple ways to implement it. However, if the design is unreasonable, it will result in duplicate values or columns. We can also design this way, put customer information and contacts into the same table. In order to solve the problem of multiple contacts, the first contact person, the first contact person, the second contact person, the second contact person, and the like can be set. If there is a third contact, a fourth contact, etc., more fields are often needed.
But if you design this way, it will cause a series of problems. For example, the customer's purchaser has a relatively large mobility and six purchasers have been replaced within one year. At this time, how should we manage it in the system? Should we establish six contact fields? This will not only lead to the increase of empty fields, but also require frequent changes to the database table structure. Obviously, it is unreasonable to do so. Some people also say that you can directly modify the buyer's name. But if you deal with this, the name of the buyer on the original purchase order will also be changed. Because the customer purchaser information on the purchase order stores in the database not the name of the purchaser, but only a number corresponding to the purchaser. When the name changes without changing the number, the changed name is displayed on the purchase order. This is not conducive to tracking at the moment.
Therefore, when designing a database, try to avoid such duplicate values or columns. The author suggests that if a database administrator encounters this situation, he can change his strategy. If you set up a separate table for the customer contact. Then connect the supplier information table with the customer contact information table through the customer ID. That is, try to place the repeated values into a separate table for management. Then connect these independent tables through views or other means.
Requirement 3: The records in the table should have a unique identifier.
When designing database tables, database administrators should develop a good habit of using an ID number to uniquely identify row records, rather than distinguishing records through 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 best to have a database to automatically manage this ID value, rather than give this task to the foreground application. Otherwise, it is easy to cause inconsistent ID values.
In addition, it is best to add line numbers when designing the database. For example, in sales order management, the ID number cannot be maintained by the user. However, the line number user can maintain it. For example, in a sales order line, the user can sort the order line by adjusting the line number. Normally, the ID column is progressive in units of 1. However, line numbers must be progressive in units of 10. In this way, under normal circumstances, the line number will be expanded in sequence 10, 20, and 30. If the user needs to adjust the record with line number 30 to the first line to display. At this time, the user can change the row number to achieve the implementation if the ID column cannot be changed. If you can change the line number to 1, you can sort it by line number when sorting. In this way, the original record with line number 30 now becomes 1 and can be displayed in the first line. This is an effective addition to the ID column in actual application design. This content is not available in textbooks. This technique needs to be mastered in actual application design.
Requirement 4: Database objects must have a unified prefix name.
A relatively complex application system often has thousands of corresponding database tables. It may be more difficult to let the database administrator understand the role of the database object by seeing the object name. Moreover, when database objects are referenced, database administrators will also have headaches for not being able to quickly find the required database objects.
To this end, the author established that it is best to spend a certain amount of time before developing a database object to formulate prefix naming specifications. For example, when designing a database, I like to negotiate with the front desk application to determine reasonable naming specifications. The author most commonly uses it to define the background database object prefix name based on the module of the foreground application. If the tables related to the material management module can be prefixed with M; if the tables related to the order management, C can be prefixed with C. What prefix is used can be defined based on the user's hobbies. However, it should be noted that this naming specification should reach a consensus between the database administrator and the foreground application developer, and the object name should be defined strictly in accordance with this naming specification.
Secondly, tables, views, functions, etc. should also have unified prefixes. For example, the view can be prefixed with V, while the function 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: Try to store only data of a single entity type.
The entity type and data type are not the same as the data type, so please pay attention to the distinction. The entity type mentioned here refers to the object itself that needs to be described. I would like to give an example, and I guess you can understand the content. For example, there is a library system now, which has two entity objects: basic book information and author information. It is also OK if the user wants to put the information of these two entity objects in the same table. For example, you can design the table as a book name, book author, etc. However, if this design is done, it will cause a lot of trouble to subsequent maintenance.
If a book is published in the future, it is necessary to add author information to each published book, which will undoubtedly add additional storage space and also increase the length of the record. Moreover, if the author's situation changes, if the address changes, you still need to change the records 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 requirements of standardized database design.
When encountering this situation, the author suggests that the above table can be broken down into three independent tables, namely the book basic information table, the author basic information table, the book and author corresponding table, etc. After this design, all the problems encountered above will be solved.
The above five are the basic requirements for achieving standardization levels when designing databases. In addition to these, there are many detailed requirements, such as data types, stored procedures, etc. Moreover, database specifications often do not have strict technical restrictions, and mainly rely on the accumulation of daily work experience of database administrators.
Requirement 1: Empty columns should be avoided in the table.
Although empty columns are allowed in the table, empty fields are a relatively special data type. When processing the database, special processing is required. In this way, the complexity of the database processing records will be increased. When there are more empty fields in the table, the performance of database processing will be much lower under the same conditions.
Therefore, although empty fields are allowed in the table when designing database tables, we should try to avoid them. If you really need it, we can handle these empty fields in some compromises to minimize their impact on database performance.
First, by setting the default value form, avoiding the generation of empty fields. For example, in a personnel management system, sometimes the ID number field may be allowed to be empty. Because not everyone can remember their ID number. When the employee reports, he may not have his ID card with him. Therefore, the ID number field is often not available in time. To do this, the ID number field can be allowed to be empty to meet the needs of these special circumstances. However, when designing a database, some processing can be done. If the user has not entered content, the default value of this field is set to 0 or N/A. To avoid the generation of empty fields.
Second, if there are many empty columns in a table, which are close to one-third of the total number of columns in the table. Moreover, these lists are optional in most cases. If the database administrator encounters this situation, the author recommends creating another subtable to save these columns. Then, the main table is associated with this secondary table through keywords. Storing data in two separate tables makes the design of the main table simpler, and can also meet the needs of storing null value information.
Requirement 2: The table should not have duplicate values or columns.
For example, there is now a purchase and sales management system, and there is a product basic information table in this system. This product development can sometimes be done by one person, and sometimes it requires cooperation from multiple people to complete. Therefore, in the product developer field of the product basic information table, sometimes multiple developers' names may need to be filled in.
For example, in the management of inventory and inventory, the customer's contacts also need to be managed. Sometimes, a company may only know the name of a customer and buyer. However, when necessary, the company needs to jointly manage the customer's 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 shipment form, you may need to fill in the name of the warehouse manager, etc.
To solve this problem, there are multiple ways to implement it. However, if the design is unreasonable, it will result in duplicate values or columns. We can also design this way, put customer information and contacts into the same table. In order to solve the problem of multiple contacts, the first contact person, the first contact person, the second contact person, the second contact person, and the like can be set. If there is a third contact, a fourth contact, etc., more fields are often needed.
But if you design this way, it will cause a series of problems. For example, the customer's purchaser has a relatively large mobility and six purchasers have been replaced within one year. At this time, how should we manage it in the system? Should we establish six contact fields? This will not only lead to the increase of empty fields, but also require frequent changes to the database table structure. Obviously, it is unreasonable to do so. Some people also say that you can directly modify the buyer's name. But if you deal with this, the name of the buyer on the original purchase order will also be changed. Because the customer purchaser information on the purchase order stores in the database not the name of the purchaser, but only a number corresponding to the purchaser. When the name changes without changing the number, the changed name is displayed on the purchase order. This is not conducive to tracking at the moment.
Therefore, when designing a database, try to avoid such duplicate values or columns. The author suggests that if a database administrator encounters this situation, he can change his strategy. If you set up a separate table for the customer contact. Then connect the supplier information table with the customer contact information table through the customer ID. That is, try to place the repeated values into a separate table for management. Then connect these independent tables through views or other means.
Requirement 3: The records in the table should have a unique identifier.
When designing database tables, database administrators should develop a good habit of using an ID number to uniquely identify row records, rather than distinguishing records through 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 best to have a database to automatically manage this ID value, rather than give this task to the foreground application. Otherwise, it is easy to cause inconsistent ID values.
In addition, it is best to add line numbers when designing the database. For example, in sales order management, the ID number cannot be maintained by the user. However, the line number user can maintain it. For example, in a sales order line, the user can sort the order line by adjusting the line number. Normally, the ID column is progressive in units of 1. However, line numbers must be progressive in units of 10. In this way, under normal circumstances, the line number will be expanded in sequence 10, 20, and 30. If the user needs to adjust the record with line number 30 to the first line to display. At this time, the user can change the row number to achieve the implementation if the ID column cannot be changed. If you can change the line number to 1, you can sort it by line number when sorting. In this way, the original record with line number 30 now becomes 1 and can be displayed in the first line. This is an effective addition to the ID column in actual application design. This content is not available in textbooks. This technique needs to be mastered in actual application design.
Requirement 4: Database objects must have a unified prefix name.
A relatively complex application system often has thousands of corresponding database tables. It may be more difficult to let the database administrator understand the role of the database object by seeing the object name. Moreover, when database objects are referenced, database administrators will also have headaches for not being able to quickly find the required database objects.
To this end, the author established that it is best to spend a certain amount of time before developing a database object to formulate prefix naming specifications. For example, when designing a database, I like to negotiate with the front desk application to determine reasonable naming specifications. The author most commonly uses it to define the background database object prefix name based on the module of the foreground application. If the tables related to the material management module can be prefixed with M; if the tables related to the order management, C can be prefixed with C. What prefix is used can be defined based on the user's hobbies. However, it should be noted that this naming specification should reach a consensus between the database administrator and the foreground application developer, and the object name should be defined strictly in accordance with this naming specification.
Secondly, tables, views, functions, etc. should also have unified prefixes. For example, the view can be prefixed with V, while the function 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: Try to store only data of a single entity type.
The entity type and data type are not the same as the data type, so please pay attention to the distinction. The entity type mentioned here refers to the object itself that needs to be described. I would like to give an example, and I guess you can understand the content. For example, there is a library system now, which has two entity objects: basic book information and author information. It is also OK if the user wants to put the information of these two entity objects in the same table. For example, you can design the table as a book name, book author, etc. However, if this design is done, it will cause a lot of trouble to subsequent maintenance.
If a book is published in the future, it is necessary to add author information to each published book, which will undoubtedly add additional storage space and also increase the length of the record. Moreover, if the author's situation changes, if the address changes, you still need to change the records 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 requirements of standardized database design.
When encountering this situation, the author suggests that the above table can be broken down into three independent tables, namely the book basic information table, the author basic information table, the book and author corresponding table, etc. After this design, all the problems encountered above will be solved.
The above five are the basic requirements for achieving standardization levels when designing databases. In addition to these, there are many detailed requirements, such as data types, stored procedures, etc. Moreover, database specifications often do not have strict technical restrictions, and mainly rely on the accumulation of daily work experience of database administrators.