Selection of primary key field types in database design

Have not written a blog for a long time, from the last published article to now is two months, has always wanted to write something, but there is no time (in fact, is an excuse), the essay content is undoubtedly the summary of work learning, experience sharing, but also a mirror of their own growth, good, say regulation Dhamma biography, Let's talk about the selection of the primary key field type in the database design.

Web development, often to interact with the database, the choice of database primary key is still important, how to choose the type of database primary key field, mainly from the following aspects of consideration:

1. First to meet business needs, this is an important starting point in the design

2. Database migration issues, consider whether to migrate frequently at a later stage, database height uniqueness

3. The extent of the use is more simple, easy (such as paging, usually based on primary key query to pagination)

4. The efficiency of database query, when the primary key is a foreign key linked list query

Depending on the above, the type of the database primary key field is often designed to be an int or GUID or a custom format type, which details the differences and application scenarios

1. Primary key field type set to int type

This situation mainly takes into account the program paging, sorting (by the primary key field increment or decrement), concise and easy to understand, but also add ROWGUIDCOL column, if SQL 2005 can use NEWSEQUENTIALID () to build in sequence, to prepare for synchronization, data merge can also be used as a reference. The Master-Slave Table association speed will be a bit faster. Small library recommended.

2. Primary key field type set to GUID type

This situation is mainly due to the strong uniqueness of the data requirements, such as the user table of the primary key type is set to a GUID, the other table's primary key type should also be set to a GUID, strictly avoid data duplication, and easy to locate; Data to be migrated frequently, if it is an int type, easy to cause confusion; There are several tables to eat similar data, sometimes to merge or other processing of these data, when the GUID type is the best choice

Summarize:

Use GUIDs. There is no duplication problem, and data merging is very simple. The association speed is slower than int. It is best to use this scenario for frequently merging data or offline input data online uploads.
The GUID value is random and does not accept any form that can be more meaningful to the user. There is no way to determine the order in which the GUID values are generated, so it is not suitable for existing applications that increment index key values in the loop order

These two options are based on different needs.

Selection of primary key field types in database design