Large-scale ERP and other database systems of several common design, since the growth of primary key, avoid using composite primary key (compound primary key), pairs of primary key, get the database to avoid a large amount of data, taking large amounts of data must use paging.
To avoid using composite primary key (compound primary key): This is mainly because of performance issues. Data retrieval is to use a large number of primary key value, simply to compare a number of fields in the field much faster than the comparison. Using a single primary key from a programming point of view is also very good, sql statement, where the conditions can write less code, which means that the chance of error greatly reduced. 3. Pairs of primary key Pairs of the primary key is the database table has two fields, the two primary key fields and become independent, but they exist. Database system with a dual primary key first in the user management module. The earliest reference to the source may be the operating system user management module
Since the growth of primary key: Primary key using self-growth is mainly performance. Early database systems, often using some numbers, such as identity card numbers, corporate numbers, etc, as the database table primary key. However, soon we discovered that these disadvantages.
Such as early hospital management system, with the identity card number as a patient table primary key. However, first, not everyone has the identity card; secondly, to foreign patients, in different countries is not necessarily the patient's identification number, and there is no duplication. Thus, identity card number as a patient table primary key is a very bad design. Taking into account no doctor or nurse would purposely go to remember those numbers, since the increase in the use of primary key is a better design.
Company Number using a specific encoding method, which is an early common practice in database systems. Its disadvantages are also obvious: I am prone to software problems, like the millennium bug, because the original design when the design of the database table the number of bits is too short, cause the system can not meet the requirements to use a few years later, and only modify the program to continue using. The problem is, any person who designed the system, the expected number of bits may be enough for such and such a number, when there is expected to not allowed to risks. The primary key is not used since the growth of the existence of such problems. By the same token, no one can go to remember such numbers. Since the increase in the use of primary key another reason is performance issues. A little programming knowledge knows that the digital size of the relatively more than the size of the string much faster. Since the increase in the use of primary key can greatly improve the speed of data to find.