table of Contents
1. Self-growth Primary Key
2. Avoid using composite primary keys
3. Dual primary key
4. Take a fixed database, table deal with changes in customer needs
5. Avoid taking a large amount of data at a time, take a lot of data must use paging.
details
1. Self-growth Primary Key
It is mainly based on the use of self-growth primary keys. Early database systems often use some number, such as ID card number, company number, etc. as the Primary Key of the database table. However, soon, everyone discovered the disadvantages.
For example, the early hospital management system uses the ID card number as the PRIMARY Key of the patient table. In the first, not everyone has an ID card; second, for foreign patients, the document number of patients in different countries is not repeated. Therefore, using the ID card number as the PRIMARY Key of the patient table is a very bad design. Considering that there is no doctor or nurse deliberately remember these numbers, using the self-growth Primary Key is a better design.
The company number uses some specific coding method, which is also common practice in the early database system. Its shortcomings are also obvious: it is easy to show software issues like thousands of years, because the design of the design is too short when designing the database table, causing the system to meet the requirements for a few years, only the modification process can continue to use. The problem is that when any person is designing the system, there is an expected risk when it is expected that a certain number of numbers can be sufficient. This problem does not exist in the use of self-growth primary keys. In the same way, no one can remember these numbers.
Another reason for the use of self-growth primary key is performance problem. Slightly programmed people know that the number size is much more than the size of the string. Use self-growth Primary Key to greatly improve data lookup speed.
2. Avoid using composite primary keys
This is mainly due to performance issues. Data retrieval is to use a large number of Primary Key values, only a field is much faster than comparing multiple fields. It is also very beneficial to use a single primary key from programming, where where the WHERE condition can write less code, which means that the opportunity of error is greatly reduced.
3. Dual primary key
The dual primary key is that the database has two fields, which are independent of the primary key, but exists at the same time. The dual primary key of the database system is earlier in the user management module. The earliest source may be the user management module of the operating system.
The user management of the operating system has two independent primary keys: the operating system automatically generates the random ID (Linux, Windows SID), Login ID. Both the two IDs must be unique, different, delete the user TEST and add a user TEST, SID different, the login ID is the same. The main purpose of using the double main key is to prevent the same Login ID caused by adding the same Login ID. For example, sales managers Hellen native sharing files give general manager Peter, leaving the company a year later, come in a normal employee Peter, two peter use the same login ID, if only the login ID is used as a user management primary key for the operating system, then There is a vulnerability: Ordinary employee Peter can access documents originally only the general manager can see. The operating system automatically generates a random ID of the random ID below to see if the user can be seen.
The dual primary key is now widely used in various database systems, not limited to the user management system.
4. Take a fixed database, table deal with changes in customer needs
This is mainly based on the following factors:
4.1 Normal use of large EPR systems, maintenance requires software vendors and its many partners to provide technical services to customers, including a large number of secondary development. If users need to add new tables or databases during software, they will bring problems to software vendors and their many partners. 4.2 The need for software upgrades. No software allows customers to use dozens of hundreds of years without upgrading. Software upgrades often involve changes in database table structures. Software vendors will make additional programs to upgrade the database data of earlier versions to new versions, but it is more difficult to process the table generated during the user.
4.3 The need for software development. Using a fixed database library table from development, second development, it is easier. For the table generated during the user, check the table name each time you find the data, then find the data, it is more troublesome.
For example, early UF financial software uses Access as a database and establishes a new database annually. Soon, users and UF companies have found that cross-year data analysis is difficult to do. So this is a bad design. In ERP, few different annual data separately separate. In general, data in all ages are in the same table. For multinational companies and even the entire group company, all companies' data is together. This benefit is that data analysis is easier to do.
Most database systems can now be used to return a certain amount of data in a constant time. For example, in the Oracle database, 10 data is taken in 1 million data according to the Primary Key, and 10 data is taken in 100 million data, and there is not much difference.
5. Avoid taking a large amount of data at a time, take a lot of data must use paging.
This is basically the basic code of many database system designs. There are many tables for more than 1 million data in the ERP system. For any one in many tables, all of them will cause the database server for a long time to be stagnant and affect the system response speed of other online users.
In general, daily operations, under the case of paging display, each time the data is between 1-100, the system response speed is fast enough, and the client is basically no particular long pause. This is a more ideal design. This is also a large database system often uses the database coupling components that are generally used by ODBC, ADO, and the like without having a specific speed of fast-selling dedicated database coupling components. Because the system bottleneck is in the database (Data amount), not the client (only a small amount of data each time).
In the B / S database system, paging is very common. Early database systems often have a one-time quantity of data in a client program to make a buffer. It is no longer necessary, main reasons:
5.1 The buffer technology of the database itself is greatly improved. Most databases will automatically put commonly used data in memory to improve performance.
5.2 Buffer technology of database joint components is also increasing. Some database coupling components including ADOs automatically buffer the result set, and the effect is good. More novel database coupons, such as Hibernate, have also joined some data results set buffering functions. Of course, there are also some database coupling components that do not buffer the data results set, such as JDBC Driver, but the situation should be changed within a few years. Some data buffers that are not very successful, such as entry beans in EJB, performance is not satisfactory, entity bean data is placed in memory, possibly because of excessive memory.
Relatively speaking, today's programmers write client data buffers, which can exceed the above two buffers, it is more difficult.
