Common designs for large ERP and other Database Systems

1. Self-increasing primary key

The primary advantage of the Self-increasing primary key is performance. Early database systems often used some numbers, such as ID card numbers and company numbers, as the primary key of the database table. However, soon, you will find the disadvantages.

For example, in the early Hospital Management System, the ID card number is used as the primary key of the patient table. However, first, not everyone has an ID card. Second, the document numbers of patients from different countries in foreign countries are not necessarily repeated. Therefore, using the ID card number as the primary key of the patient table is a very bad design. Considering that no doctor or nurse would deliberately remember these numbers, using the self-increasing primary key is a better design.

Company numbers use a specific encoding method, which is also a common practice in early database systems. Its disadvantages are also obvious: it is easy to see software problems such as the millennium bug, because the design of database tables was too short, resulting in the system cannot meet the requirements after several years of use, only modificationProgramCan continue to use. The problem is that when anyone designs a system, there is an unexpected risk when the number of digits of a certain number can be enough. This problem does not exist if you use a self-increasing primary key. In the same way, no one can remember these numbers.

Another reason for using a self-increasing primary key is performance problems. Anyone with a little programming knowledge knows that numbers are much faster than strings. Using a self-increasing primary key can greatly increase the data search speed.

2. Avoid using compound primary key)

This is mainly because of performance problems. Data retrieval requires comparing a large number of primary key values. Comparing only one field is much faster than comparing multiple fields. The use of a single primary key is also very beneficial from the programming point of view, SQL statements can write less where conditionsCodeThis means that the chances of errors are greatly reduced.

3. Double primary key

A dual-primary key refers to two fields in the database table, which are independent of the primary key, but exist at the same time. The dual-Primary keys of the database system were first used 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 random ID (Linux, Windows Sid) and login ID automatically generated by the operating system. The two IDs must be unique. The difference is that the user test is deleted and a user test is added. The SID is different and the login ID is the same. The primary purpose of using a double primary key is to prevent confusion caused by adding the same Login ID after deletion. For example, sales manager hellen shares a local file to the general manager Peter. One year later, the general manager leaves the company and comes in to an ordinary employee Peter. The two Peter uses the same Login ID, if you only use login ID as the user management primary key of the operating system, there is a vulnerability: Peter, a common employee, can access files that can only be viewed by the general manager. The random ID automatically generated by the operating system is generally invisible to users.

Dual-Primary keys are now widely used in various database systems and are not limited to user management systems.

4. Fixed databases and tables to cope with changing customer needs

This is mainly based on the following considerations:

4.1 normal use and maintenance of large EPR systems require software vendors and their partners to provide technical services to customers, including a large number of secondary development projects.

If you need to add a new table or database during normal use of the software, it will bring difficulties to the software vendors and their partners.

4.2 software upgrade needs.

There is no software that allows customers to use dozens of years without upgrading. Software upgrades often involve changes to the database table structure. The software manufacturer will upgrade the database data of earlier software versions to a new version, but it is difficult to process the tables generated during user use.

4.3 software development needs.

It is easier to use fixed database tables for development and secondary development. For tables generated during the user's use process, you must first look up the table name and find the data each time you search for the data, which is troublesome.

For example, the early UF financial software used access as a database to create a new database every year. Soon, users and yonyou found that cross-year data analysis was difficult. Therefore, this is a poor design. In ERP, there are very few separate annual data. Generally, the data of all years is in the same table. When multinational companies and even the entire group company use the same ERP system, all the company's data is together. The advantage is that data analysis is easier.

Currently, most database systems can return a certain amount of data within a constant time. For example, in an Oracle database, 10 pieces of data are retrieved from 1 million pieces of data based on the primary key, and 10 pieces of data are retrieved from 0.1 billion pieces of data. The time difference is not much.

5. Avoid getting a large amount of data from the database at a time, and retrieve a large amount of data by page.

This is basically the basic code designed by many database systems. There are many tables with more than 1 million data records in the ERP system. For any of the many tables, retrieving all data at a time will cause the database server to be stuck for a long time, it also affects the system response speed of other online users.

In general, in daily operations, the system response speed is fast enough for each data acquisition between 1-100 in the case of paging display, and the client basically does not have a particularly long pause. This is an ideal design. This is also the reason that large database systems often use ODBC, ADO, and other general database connection components instead of specific dedicated database connection components that are faster. Because the System Bottleneck lies in the database (large data volume), not the client (the client only obtains a small amount of data at a time ).

Paging is common in B/S database systems. Early database systems often cache a large amount of data from client programs at a time. This is not a special need. The main reasons are:

5.1 The buffer technology of the database itself is greatly improved.

Most databases automatically buffer frequently used data in the memory to improve performance.

5.2 The buffer technology for database connection components is also improving.

Some database connection components, including ado, will automatically buffer the result set, and the effect is good. A relatively new database connection component, such as Hibernate, has also added some data result set buffering functions.

Of course, some database connection components do not buffer the data result set, such as JDBC driver, but the situation should change in a few years. There are also some inefficient data buffering, such as the entity bean in EJB, which has unsatisfactory performance. The entity bean data is also stored in the memory, probably because it occupies too much memory.

Comparatively speaking, it is difficult for programmers to write client data buffering results that exceed the above two buffering effects.