Two important requirements of Oracle Database Design Standardization

The following articles mainly introduce the five specific requirements of Oracle Database in design standardization. Today we mainly introduce the first two requirements, we should avoid empty columns in the table, and avoid repeated values or column content in the table.

In general, we can judge from two aspects whether the Oracle database is designed with a comparative specification. 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, Oracle 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, they are close to 1/3 of the total number of columns in the table. In most cases, these columns are dispensable. If the Oracle 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 Oracle 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 Oracle 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.