About choosing a primary key for a database

Database primary key Selection (original)

Worked for so many years what the database of the primary key has been used, various kinds of project key changes, mishap and so on have met.

Off with primary key first do not use the whole system only one way of primary key, to thoroughly thoroughly understand the business requirements, according to the needs of each table to consider how to design the primary key can give you the greatest benefit.

There are four common types of primary keys commonly used in databases: self-increment number, Guid, custom unique, federated primary key. The design of database primary key, if the design is reasonable, will bring many advantages to the whole system. For example: Reduce the development difficulty writing a lot of code, database retrieval efficiency, portability, scalability, maintainability, legibility, etc... For the primary key, there is no best, only the most suitable.

Below I based on my experience in the form of scoring to say their own views, by the way to introduce the use of the scene, here is the main comparison of "self-increment number" and "GUID", if there are different views of the hope that you can message more exchanges.

A. Comparison

1. Efficiency: Self-increment number since the use of int numeric type Plus is ordered storage, whether it is the retrieval, Foreign Key association, or operation add and delete performance is the highest. Of course, if the customization is unique, it is also highly efficient to use data types if it is guaranteed to be orderly. GUIDs are unordered, and search speeds such as varchar types are certainly not as high as int.

Self-increment number (5 points), GUID (2 points), custom unique (3 points, ordered + 1, data type + 1), Federated Primary Key (1~4 points)

2. Occupy space: Since the increment number is generally int is 4 bits, the GUID is generally 32 bits, it is visible if the table int with millions of data will save a lot of space. (Of course, the current era of hard disk is easier than more than 10 years ago more and more basic can be negligible). Note: Here is a supplementary sentence if you consider the traffic is to consider, for example, your terminal needs to have a transaction with the service interface need to transfer 1000 data primary key, if the use of int will save a lot of traffic here.

Self-increment number (5 points), GUID (2 points), custom unique (look at the length of your design 1~4), Federated primary KEY (see the length of your own design 1~4)

3. Security: The only security I have here is that in the BS project, we often pass some primary keys between the page and the page, or between the page and the daemon interface, to achieve the desired goal. If all the parameters are not encrypted, then the int type can easily be associated with other data, and can manipulate the database by changing the URL parameters on the URL parameters. GUIDs are hard to guess when associating data with other primary keys.

Self-increment number (1 points), GUID (5 points), custom unique (see case), Federated primary Key (see case)

4. Programmability: The GUID can be generated before the database operation of the GUID, before the operation of the database can be assigned to other from the table, so that a transaction commit operations database. The self-increment number is to be pre-inserted to get a primary key value returned by the database, and then to other foreign keys from the table, and then commit the operation database. It is also used to generate one interaction with the database by using the transaction self-increment number more than the GUID. Affect program execution performance. It is done in a distributed development project. This is a very infeasible way to do it. The more frequent the transmission over the network, the more often it proves the risk execution success rate.

Self-increment number (1 points), GUID (5 points), custom unique (see case), Federated primary Key (see case)

5. Extensibility: This is primarily only for example, client business data is aggregated to a single table on the server. or Excel and other data import, etc... Because the GUID is the only advantage in the world is obvious, the self-increment number is flawed, because the client local database is numbered starting from 1 so doing the server-side rollup to a table is definitely not achievable.

Self-increment number (1 points), GUID (5 points), custom unique (see case), Federated primary Key (see case)

There are some other aspects of the small details we can do in peacetime design and development of more summary.

Two. Usage Scenarios

1. Self-increment number: Generally used for table operation simple single No transaction operations, such as the system's Basic Information Table Code table, menu table, etc...

2.GUID: Generally used for table business operations complex involves doing the primary key from the table, or table import Export modify backfill operations, or table do distributed development requires the client to the service-side table summary operations

3. Custom unique: If the system according to the business requirements can make a unique system of the primary key is also very good, mainly to consider large concurrency to obtain the primary key can be resolved here to solve with this is the best.

4. Federated PRIMARY Key: The system primary key involves using this in a meaningful way. For example, you have 100 clients, each with a different abbreviation for the name of the client. You can use the client abbreviation + order number two columns to make a federated primary key. However, the Federated primary Key is developed by the time it is from the table to correspond to the main table is the union of the primary key outside the number of joint foreign keys will be written.

Three. Summary

1. Sometimes you use the GUID key, you can also add a self-increment number example in the same table, as long as it is useful to your system has the value of existence.

2. Guid is recommended for systems that often require data migration. And adding nonclustered indexes to the corresponding foreign key fields, that is, the fields used to make the connection query, is of great benefit to improving performance. A field in the Where condition can also add a nonclustered index appropriately.

3. When using the GUID type as the primary key, the data type should be uniqueidentifier, and be sure to remember to cancel the "clustered index" of the primary key

4. For not need to do the migration, or small system, with the INT key is very convenient, and in terms of efficiency is still a certain increase.

5. It is best not to use a self-increment number to encounter bottlenecks if you are involved in large concurrent execution of insert data. It is recommended that you use GUIDs for large concurrency.

About choosing a primary key for a database