Oracle Database design should be based on five rules (1)

Guidance:When I work with colleagues or communicate with relevant colleagues on a forum, I always find that some people have bad Oracle database design habits and affect the database performance, increases the workload of database administrators. The following describes the security zones that should be avoided during Oracle database design,

As we all know, database design is directly related to the efficiency of database operation. Based on my experience, the rational database design is more effective than upgrading the server hardware configuration for improving database performance. However, when I work with colleagues or communicate with relevant colleagues on a forum, I always find that some people have bad database design habits, it affects the database performance and increases the workload of the database administrator.

The author believes that in order to improve the performance of the database, the Oracle database should be designed to achieve five lessons.

　Caution: do not create indexes on small tables.

Undoubtedly, indexes can improve the efficiency of database queries. However, as the saying goes, it is far behind. Indexes must also be used in a timely manner. If the index is improperly set, it will not improve the database performance, but will play the opposite role. For example, you can set indexes on small databases, and these tables are frequently changed. For example, the basic employee information table contains no more than 10 simple fields. This table needs to be inserted and deleted frequently. When performing these changes, you must maintain the indexes. This maintenance workload may consume more storage space than the scan tablespace. This not only starts to improve the database performance, but also slows down.

Therefore, when designing a database, the first rule is not to create indexes on small tables that users often change. Otherwise, it is not worth the candle.

Secondary warning: Do not use the user's key.

For example, we have a sales order table when designing an ERP system database. In this table, there is a sales order number. Can we use this ticket number as the foreign key to associate other tables? For example, you need to associate it with the sales order on the sales delivery order. In this case, can we use the sales order number as the keyword associated with the delivery order?

The answer is yes, but it is not the best choice. Let's take a look at the ERP background database. In the sales order table, in addition to the only field that indicates the sales order record, there is also a field that is the sales order ID. Even though the sales order number is displayed on the invoice page at the front end, the sales order ID is stored in the background. That is to say, the database does not use the user's key as the primary key, but uses the Document ID field automatically maintained by the database.

Why is it designed like this? This is the second rule I want to talk about today. Do not use the user's key. Generally, do not select an Editable field as the foreign key or primary key. This will increase our additional workload.

If we use the sales order number as the foreign key, we also need to impose restrictions on the user's field editing behavior after creating the sales order record, such as determining whether the foreign key is in violation of the mandatory rules. If some systems set the sales order number as a foreign key, they usually set this field as the system's automatic number and the user cannot change it. However, in actual work, enterprise employees often need to edit this field. The system lacks flexibility when employees need to edit these uneditable fields. Moreover, when the user enters the data to be saved and prompts that the record does not meet the requirements, it is not a user-friendly design.

In addition, we must design some methods for detecting and correcting key conflicts. For example, whether the foreign key exists in other data tables. Although this usually takes us some time to complete. However, in terms of database performance, this cost is relatively high. In this case, the basic data of the system cannot be well isolated from the data of enterprise employees.

Therefore, I believe that we should not use the user's key as the primary key or foreign key designed for our database. In other words, the keys used in database design must be automatically maintained by the database system. You cannot change this maintenance rule.