The use of self-growth is mainly performance

Early database systems, often using some kind of numbering, such as identity card numbers, company numbers, etc. as the database table

However, soon, everyone found that the disadvantages

For example, the early hospital management system, the use of identity card numbers as the patient table

However, the first that not everyone has an ID card; second, for patients coming from abroad, there is not necessarily no duplication of ID numbers for patients from different countries

So, using ID numbers for the patient table is a very bad design

Considering that no doctors or nurses deliberately memorize these numbers, using self-growth is a much better design

Company numbering employs a somewhat specific encoding method, which was common in early database systems

The downside of this is also obvious: it's prone to software problems like Y2K, where the number of digits designed to be too short when the database tables were originally designed led to a system that couldn't meet the requirements after a few years of use, and the only way to keep using it was to modify the program

The problem is that anyone designing a system with an the risk of not being able to predict how many digits of so-and-so's number will be enough

This is not a problem with self-growth

And by the same token, no one can memorize these numbers

Another reason to use self-growth is performance

Anyone with a little bit of programming knowledge knows that comparing the size of a number is much faster than comparing the size of a string

Another reason for using self-growth is performance. much faster

Using self-growth can greatly improve the speed of data lookup

2

Avoid using composite primary key (compound) This is mainly because of performance issues

Data retrieval is to use a lot of value comparisons, compare only one field is much faster than comparing multiple fields

Using a single from the programming point of view is also very good. The where condition in the sql statement can be written in less code, which means that the chance of error is greatly reduced

3

Double primary keyDouble primary key refers to a database table that has two fields that are independently the primary key, but exist at the same time

Double primary keys in database systems were first used in the user management module

The earliest source is probably the Refer to the user management module of the operating system

The user management of the operating system has two independent primary keys: a random ID automatically generated by the operating system (SID for Linux and Windows), and a loginid

These two IDs have to be unique, but the difference is that deleting the user test and then adding a new user test has a different SID and the loginid is the same

The two primary keys are the same. different, the same loginid

The main purpose of using a double primary key is to prevent confusion caused by deleting and then adding the same loginid

For example, the sales manager hellen local **** enjoy the file to the general manager of the peter, a year after the general manager to leave the company, came in an ordinary employee peter, two peter with the same loginid, if you only use the loginid as the operating system's user management primary key, there is a loophole: ordinary employees peter can access the original only the general manager can look at the file

Operating system automatically generated by their own random ID is generally not visible to the user below

Double primary key has now been widely used in a variety of database systems, not limited to User Management System

4

To cope with the changing needs of customers with a fixed database, table this is mainly based on the following factors: 4

1 the normal use of large-scale EPR systems, maintenance requires the software vendor and its many partners **** the same to provide technical services to the customer, including a large number of secondary development

If the user needs to add a new user in the normal use of software

The user can also add a new user in the process, the user can also add a new user in the normal use of the software, the user can also add a new user. If users need to add new tables or databases in the normal use of the software, it will bring problems to the software vendors and their many partners

4

2 The need for software upgrades

No software can be used by customers for tens of hundreds of years without having to be upgraded

Software upgrades often involve a change in the structure of the database tables

Software vendors will do additional procedures to upgrade the database data of earlier versions of software to the new version, but it is more difficult to deal with the tables generated by users in the process of using

4

3 The need for software development

It is easier to use a fixed database table from the development of secondary development

For the tables generated by users in the process of using the data, every time you look for the table name, you have to check the table name first. data, you have to look up the table name first, and then find the data, which is more troublesome

For example, the early UFIDA financial software used Aess as the database, and a new database was created every year

Rapidly, users and UFIDA found that it was difficult to analyze the data across the years

So this is a bad design

In the ERP, it is rare to have the different year data separately

Generally, all the years are in the same table

For multinational companies or even the whole group of companies using the same ERP system, all the companies' data are together

The advantage of this is that the data analysis is easier to do

Nowadays, most of the database systems are able to do it in a constant amount of time return a certain amount of data

For example, in the Oracle database, based on taking 10 pieces of data out of 1 million pieces of data, and taking 10 pieces of data out of 100 million pieces of data, the difference in time is not that much

5

Avoid taking a large amount of data from the database at one time, and take a large amount of data and make sure to use paging

This is basically the basic code of design for a lot of database systems nowadays

This is basically the basic code of design for many database systems nowadays.

ERP systems in more than 1 million pieces of data in the table a lot of, for any of the many tables, once to take all the database server will lead to a long time at a standstill, and affect the other online users of the system response speed

Generally speaking, the daily operation, in the case of paging, each time to get the data in between 1 and 100

Because the bottleneck lies on the Database side (large amount of data), not on the client side (the client only fetches a small amount of data at a time)

In B/S database systems, paging is very common

Early database systems often had client programs that fetched a large amount of data at a time for buffering

Now it is no longer particularly necessary, mainly because:5

1 The database's own Buffering technology is greatly improved

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

5

2Database linking components are also improving buffering technology

Some database linking components, including ADO, automatically buffer the resultset and the results are good

Some database linking components, including ADO, automatically buffer the results of the data.

Newer database joins, such as Hibernate, also incorporate some resultset buffering

Of course, there are some database joins that don't buffer result sets, such as JDBCDriver, but the situation should improve in a few years

There are also less successful data buffering

Relatively speaking, today's programmers writing client-side data caching that exceeds the effects of the above two caches are already having a harder time