Oracle Database design should be able to achieve

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.

The third rule: Do not use business rules to achieve data integrity.

There are several implementation methods for data integrity. For example, data integrity can be achieved through database constraints, or data integrity can be achieved through business rules of the foreground system. However, I would like to suggest that in some large databases, do not try to use business rules to achieve data integrity, but try to use database constraints. If you use business rules to implement integrity, some inexplicable errors may occur.

For example, I have encountered this case. During database design, the length of a reserved field is limited to 50 characters at most. In front-end applicationsProgram, But the limit is 60 characters. You can enter 55 characters in front-end applications for employee data. However, the next time the user queries, he finds that the last few characters are missing, leaving only the previous content. This is mainly because the maximum number of digits in the database is exceeded during data storage. The database automatically removes the last few characters and saves them. In this way, you can save the input data at the front-end. However, in fact, the data stored in the database is incomplete.

Therefore, the author's third rule is not to use business rules to achieve data integrity at the front-end. As long as possible, we should try to achieve data integrity at the database system level. When implementing data integrity at the database level, you must pay attention to user friendliness.

First, the violation information should be returned to the user as detailed as possible. For example, if the sales order number is set, the uniqueness constraint is set in the database system. When a user creates a sales order and the sales order number is repeated, the detailed information of this constraint needs to be fed back to the front-end user. It is often not enough to display only the error information that cannot be saved. This will make the user confused.

Second, when necessary, you can use triggers to achieve data integrity. Although in terms of function design, we do not recommend using triggers in Oracle databases. To convert a lower-case amount to a larger amount. We recommend that you use the foreground application. Because the application execution efficiency is higher than the database trigger. However, in terms of database data integrity, when necessary, if the integrity cannot be achieved by database constraints, we still recommend using triggers instead of foreground applications. In any case, do not rely on applications for data integrity. Otherwise, it is difficult to ensure consistency between database constraints and business-layer constraints.

4. Do not allow foreground applications to directly access data tables if possible.

In fact, there is a good "man-in-the-middle" between the basic database table and the foreground application, that is, the view. The attempt is an abstraction of the basic database table. In addition to ensuring data quality, he can also effectively access illegal data access by foreground applications. Therefore, some database systems with high security levels tend to be in the database and foreground applications during database design.CodeProvide the "View" intermediary between them to serve as the "spokesperson" of the basic table ". In this way, foreground applications can access tables indirectly through views without having to access them.

For example, when designing a data import program, it is ideal to use a view. For example, you need to import product information, including basic product information, supplier information, and price information. The information is usually contained in three tables: basic product information table, supplier information table, and product price information table. If the foreground application directly accesses the basic table, you need to access the three basic tables respectively. This is not only inefficient, but also difficult to achieve data integrity. In this case, we can use the view to combine some fields that must be filled in into an updatable view. In this way, the foreground application only needs to access one database object.

Furthermore, the design of database reports must be implemented through views. Instead of passing a piece of SQL code to query the basic table. This poses a big threat to data security.

Therefore, the fourth rule is not to allow foreground applications to directly access data tables if possible.

Wujie: do not always allow users to input data.

Some fields do not have to be input by the user. You only need to select them. For example, in the personnel management system, there is no need for users to manually enter the nationality, position, and province of an enterprise employee. However, you can select an employee in the form of a list. This method is one of the best ways to improve data integrity. If you can provide users with a detailed list for their selection, this will reduce the error of Typing Code and improve Database Consistency. It can also improve the friendliness of the user interface.

Therefore, when designing a database, try to use the list field for the user to choose. If you need to manually enter fields such as Province, it takes a lot of time to complete the database. For example, when saving data, you must determine whether the data entered by the user meets the integrity rules. This obviously requires much more work than designing a list field.

Therefore, the fifth rule in database design is not to allow users to manually enter data. The list is provided for users to choose. Users can not only improve efficiency, but also save time and ensure data accuracy.