Oracle database design to do five commandments

As we all know, the quality of database design is directly related to the efficiency of database operation. According to the author's experience, to enhance the database performance, reasonable database design, than upgrade the server hardware configuration, but also to come effective. However, the author whether in cooperation with colleagues, or in the forum with the relevant peer exchanges, will always find some people have some bad database design habits, affecting the performance of the database, increased the workload of the database administrator.

I believe that in order to improve the performance of the database, Oracle database design, to do five precepts.

One Ring: Do not index on small tables.

Undoubtedly, indexes can improve the efficiency of database queries. But, as the saying goes, the past is less than. Indexes must also be used in a timely location. If the index is not set properly, not only will not improve the performance of the database, but will play a reverse role. If you set up indexes on a small database, and those table users change more frequently. For example, the employee basic information sheet is simply no more than 10 fields. This table user needs to be inserted and deleted frequently. When these change jobs are made, the index needs to be maintained. This maintenance effort may consume more storage space than the scan table space. This not only starts to improve the function of database performance, but is in the drag.

So, the first thing to do when designing a database is to not index on small tables that users often change. Otherwise, it is not worth the candle.

Second ring: Do not use the user's key.

If we are designing an ERP system database, we have a sales order form. In this table, there is a sales order number. So can we use this number as a foreign key to the other table? If you are on a sales order, you need to associate it with the sales orders. At this time, can we take the sales order number as the key word associated with the shipping list?

The answer is yes, but it is not the best choice. We can look at the background database of ERP. On the sales order form, in addition to the sales order number, which is the only field that represents the sales order record, there is also a field that is the sales order ID. Although the sales order number is displayed in the front end of the shipping list, the sales order ID is stored in the background. In other words, the database is not based on the user's key as the primary key, but instead uses the database automatically maintained document ID this field.

Why is it so designed? This is the second commandment that I want to talk about today, do not use user's key. In general, do not select a field that the user can edit as a foreign key or a primary key. Because it will add to our extra workload.

If we use the sales order number as the foreign key, we will also impose restrictions on the user's editing of the field after the sales order record is created, such as determining whether to violate the mandatory rules for foreign keys, and so on. Some systems set the sales order number to foreign keys, it is often set this field to the system automatically numbered, and the user can not be changed. However, in the actual work, the enterprise staff often need to edit this field. The flaw in the system's lack of flexibility is evident when employees need to edit these non-editable fields. Moreover, when the user enters the data to save the time again prompts the record not to meet the request, then also is not very humanized design.

In addition, we must design some methods for detecting and correcting key conflicts. Consider whether this foreign key exists in other data tables, and so on. Although this usually only takes us a little time to take care of. But from the database performance, the cost is relatively large. Moreover, in this case, it is not good to put the basic data of the system and the enterprise staff data to achieve a good isolation.

Therefore, the author thinks, do not use the user's key as our database design primary key or foreign key. Or, the database design uses the key to make the database system for automatic maintenance, users must not change this maintenance rules.