Five requirements for standardized Oracle Database Design

Generally, you can determine whether a database is designed with a comparative specification from two aspects. First, check whether there are a large number of narrow tables, and second, whether the number of wide tables is small enough. If the two conditions are met, the standardization level of the database is still relatively high. Of course, these are two general indicators. To meet the requirements of database design standardization, the following five requirements must be met.

Requirement 1: avoid empty columns in the table.

Although empty columns are allowed in the table, empty fields are special data types. The database must perform special processing during processing. In this case, the complexity of the database processing records will be increased. When there are many blank fields in the table, the database processing performance will be much lower under the same conditions.

Therefore, although empty fields are allowed in the database table design, we should avoid them as much as possible. If necessary, we can take some discounts to process these empty fields, minimizing their impact on database performance.

First, you can set the default value to avoid null fields. For example, in a personal affairs management system, sometimes the ID card number field may be blank. Because not everyone can remember their ID card numbers. However, when an employee reports, his/her ID card may be missing. Therefore, the ID card number field is often not provided in a timely manner. Therefore, the ID card number field can be empty to meet the needs of these special circumstances. However, some processing can be done during database design. If no content is entered, set the default value of this field to 0 or N/. To avoid null fields.

Second, if a table contains many columns that are allowed to be empty, it is close to 1/3 of the total number of columns in the table. In most cases, these columns are dispensable. If the database administrator encounters this situation, I suggest creating another sub-table to save these columns. Then, the master table is associated with the sub-table by using the keyword. Storing data in two independent tables makes the design of the winner table easier and meets the needs of storing null values.

Requirement 2: duplicate values or columns should not exist in the table.

For example, there is an inventory management system, which contains a basic product information table. Sometimes this product can be developed by one person, and sometimes it can be completed by multiple people. Therefore, in the product basic information table, you may need to enter the names of multiple developers.

For example, you also need to manage the customer's contacts in the inventory management. Sometimes, the company may only know the name of one buyer of the customer. However, when necessary, the enterprise must manage the customer's procurement representatives, warehouse personnel, and financial personnel. Because in the order, you may need to fill in the name of the purchase representative; but in the delivery order, you need to fill in the name of the warehouse management personnel and so on.

There are multiple implementation methods to solve this problem. However, if the design is unreasonable, duplicate values or columns may occur. For example, we can design this way to put customer information and contacts in the same table. To solve the problem of multiple contacts, you can set the first contact, the first contact phone number, the second contact, and the second contact phone number. If there are third and fourth contacts, more fields are required.

However, this design will lead to a series of problems. If the customer's purchasers are more mobile, six purchasers will be changed within one year. How can I manage it in the system? Should we create six contact fields? This will not only increase the number of blank fields, but also require frequent changes to the database table structure. Obviously, this is unreasonable. Some people say that you can directly modify the name of the buyer. However, the name of the buyer in the original purchase order will be changed. This is because the customer buyer information stored in the database is not the name of the buyer, but the number corresponding to the buyer. If the number is not changed but the name is changed, the changed name is displayed on the purchase order. This is not conducive to tracking.

Therefore, when designing a database, we should try to avoid repeated values or columns. I suggest you change the policy if the database administrator encounters this situation. For example, set another table for the customer contact. Then, the supplier information table is connected to the customer contact information table by the customer ID. That is to say, try to store duplicate values in an independent table for management. Then, the independent tables are connected by views or other means.

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

When designing a database table, the database administrator should develop a good habit of using an ID number to uniquely identify a row record, rather than distinguishing records by names, numbers, and other fields. Each table should have an ID column, and neither of the two records can share the same ID value. In addition, it is best to have a database for automatic management of this ID value, instead of assigning this task to the foreground application. Otherwise, the ID values may be inconsistent.

In addition, it is best to add row numbers when designing databases. For example, in sales order management, the ID number cannot be maintained by the user. However, you can maintain the row number. For example, in the sales order line, you can sort the order line by adjusting the size of the row number. Generally, the ID column is progressive in units of 1. However, the row number must be progressive in the unit of 10. In this case, the row numbers are expanded in order of 10, 20, and 30. If you want to adjust the record with the row number 30 to the first line. In this case, you can change the row number if you cannot change the ID column. For example, you can change the row number to 1, and sort by the row number during sorting. In this case, the record whose original row number is 30 is now changed to 1 and can be displayed in the first row. This is an effective supplement to the ID column in the actual application design. This content does not exist in textbooks. This technique can be mastered only in actual application design.

1 2 Next Page