MSSQL-Logical primary key, business primary key, and composite primary key

Reprinted from: http://blog.csdn.net/sunrise918/article/details/5575054

These days on the logical primary key, business primary key and composite primary key have some thinking, also searched the related discussion on the net, related discussion can see the bottom reference link. Here are some of your own SQL Server-based summaries, other databases (Oracle, MySQL, DB2 、...... Should be similar, too. This is only a moment of their own thinking, if there is inappropriate please inform, rethink and then revise.

Definition (partially defined from SQL Server Books Online):

Primary KEY (PRIMARY key): A table typically has a column or set of columns that contains values that uniquely identify each row in the table. Such a column or columns is called the primary key (PK) of the table and is used to enforce the entity integrity of the table.

Foreign key (FOREIGN key): A foreign key (FK) is one or more columns used to establish and strengthen links between two table data. In a foreign key reference, when a table column is referenced as a column of the primary key value of another table, a link is created between the two tables. This column becomes the foreign key of the second table.

Clustered index: The clustered index sorts and stores data rows based on their key values in the table. Each table can have only one clustered index, because the data rows themselves can only be stored in one order.

Nonclustered indexes: Nonclustered indexes contain index key values and row locators that point to the table data storage location. You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes are designed to improve the performance of frequently used queries that do not establish a clustered index.

AutoNumber columns and Identifier columns: For each table, you can create an identifier column that contains system-generated ordinal values that uniquely identify each row in the table.

Business primary KEY (natural primary key): The field with the business logic meaning in the database table as the primary key, called the "Natural primary key (Natural key)".

Logical primary KEY (proxy primary key): A field in a database table that is independent of the logical information in the current table as its primary key, called the surrogate primary key.

Composite primary KEY (Federated primary key): A primary key by a combination of two or more fields.

Principle Analysis:

The primary reason for using a logical primary key is that once the business primary key is changed, the modification of the part associated with that primary key in the system will be unavoidable, and the more references the larger the change. Using the logical primary key only needs to modify the business logic related to the business primary key, which reduces the scope of the influence on the system due to the related changes of the business primary key. The change of business logic is unavoidable, because "forever is the change", no one company is invariable, no one business is forever unchanged. The most typical example is the business change of ID card promotion and driver's license number. And in reality, there is a duplication of identity card numbers, so that if you use the identity card as the primary key also brought difficult to deal with the situation. Of course, there are a lot of solutions to change, one of which is to make a new system with the times, which is really good for software companies.

Another reason for using logical primary keys is that the business primary key is too large for transmission, processing, and storage. I think generally if the business primary key more than 8 bytes should be considered to use the logical primary key, because the int is 4 bytes, bigint is 8 bytes, and the business primary key is generally a string, the same is 8 bytes of bigint and 8 bytes of string in the transmission and processing is naturally bigint more efficient. Imagine the code = = "12345678" and the id = = 12345678 of the assembly code of the difference will know. Of course, the logical primary key is not necessarily an int or bigint, and the business primary key is not necessarily a string or an int or a datetime type, while the transmission is not necessarily the primary key, this is the specific analysis, but the principle is similar, here is just the general situation. Also, if other tables need to reference the primary key, the primary key needs to be stored, and the cost of the storage space is not the same. And this reference field of these tables is usually a foreign key, or usually will be indexed to facilitate the search, which will also result in the cost of storage space is different, this also needs to be specifically analyzed.

Another reason for using logical primary keys is that using an int or bigint as a foreign key for a join query can perform faster than joining a query with a string as a foreign key. The principle is similar to the above, which is not repeated here.

Another reason for using logical primary keys is that there is a problem in which the user or maintainer mistakenly entered the data into the business primary key. For example, the wrong to input the RMB into the RXB, the relevant reference is quoted the wrong data, once the need to modify is very troublesome. If the use of logical primary keys is a good solution, if the use of business primary key will affect the foreign key data of other tables, of course, can also be resolved by cascading updates, but not all of the energy level associated.

The main reason for using a business primary key is that adding a logical primary key is adding a business-independent field, and the user is usually looking for business-related fields (such as the employee's work number, the book's ISBN No.), so that we must index the business fields in addition to the logical primary key. This will degrade the performance of the database and increase the overhead of storage space. Therefore, the use of business primary keys is a better choice for basic data that does not change very often in business. On the other hand, for the basic data, the general increase, deletion, change are relatively small, so this part of the cost is not too much, and if this time for business logic changes have concerns, but also can consider the use of logical primary key, which requires specific analysis of the problem.

Another reason to use a business primary key is that, for user actions, it is done through a business field, so in these cases, if you use a logical primary key, you must do more mapping transformations. I think this kind of worry is superfluous, directly using the Business primary key query can get results, do not have to control the logical primary key, unless the business primary key itself is not unique. In addition, if the design of the use of logical primary key, when the code will be the primary key processing, in the system internal transmission, processing and storage are the same primary key, there is no conversion problem. Unless the existing system uses a business primary key, there is a conversion problem in order to change the existing system to use a logical primary key. For the time being, there is no such thing as a change of scene.

One more reason to use business primary keys is that security is more important to the banking system than performance, when you consider using a business primary key that can be used as a primary key or as redundant data to avoid the associated loss problems that are caused by using logical primary keys. If for some reason the relationship between the primary table and the child table is lost, the bank will face irreparable losses. To prevent this from happening, the business primary key needs to be redundant in important tables, and the best way to handle this is to use the business primary key directly. such as social Security number, Passbook number, card number and so on. So usually the banking system requires the use of a business primary key, which is not due to performance considerations but for security reasons.

The primary reason for using a composite primary key is related to using a business primary key, and usually a business primary key that uses only one field does not solve the problem, so you can use more than one field. For example, using the Last Name field is not enough, and then add a birthday. This method of using composite primary keys is very inefficient, primarily because it is similar to the case for larger business primary keys. In addition, if the other table to be associated with the table will need to reference all the fields of the composite primary key, this is not simply a performance problem, there is a storage space problem, of course, you can also think that this is reasonable data redundancy, easy to query, but feel a little outweigh the gains.

Another reason to use composite primary keys is that, for relational tables, the primary key of the two entity tables must be associated to represent the relationship between them, then the two primary keys can be combined to form a composite primary key. If there are multiple relationships for two entities, you can add a sequential field union to form the composite primary key, but this introduces the drawbacks of the business primary key. Of course, you can also add a logical primary key to the relational table, avoiding the drawbacks of the business primary key, as well as facilitating references to it from other tables.

In general, most people on the internet tend to use logical primary keys, and there should be few who agree on how to use composite primary keys for entity tables. People who support business primary keys often have the misconception that the logical primary key must be meaningful to the user, but that the logical primary key is only used internally by the system and is not known to the user.

Conclusion or inference:

1, try to avoid using business primary key, try to use logical primary key.

2, if you want to use business primary key must ensure business logic related to change the probability of the business is 0, and the business primary key is not too large, and the business primary key can not be referred to the user modification.

3, in addition to the relational table, try not to use composite primary key.

Best Practices guide for using logical primary keys:

1, enough to use good. The lifetime of the system is limited to 100 years, the logical primary key data type takes the following table rule, and the int type is used if unsure.

Data volume	Data type	Data size	Generation frequency	Note
< 128	tinyint	1 bytes	1 articles/year	Frequency is too low, not very reliable, do not recommend the use of
< 30,000	smallint	2 bytes	27 Records/month	Low frequency, use with caution
< 2.1 billion	Int	4 bytes	40 Strips/min	Can meet most of the situation
< 92.2 billion	bigint	8 bytes	2.92 million bar/ms	Can meet most of the situation
>= 92.2 billion	uniqueidentifier	16 bytes	10 billion users generate 1 billion per millisecond at a time and can generate 1 billion consecutive years	Can be used for distributed, high-concurrency applications

2, generally adopt self-growth mode or newid () way.

3, the primary key field name is generally used "Table name ID" method, easy to identify and table join.

4, if the table exists distributed application, you can consider using different starting values, the same step size self-increment. For example, there are 3 libraries deployed in different places, you can design as follows:

Starting value	Step
1	10
2	10
3	10

Step set 10 is to facilitate future expansion, so that the different libraries can also maintain the uniqueness of the primary key, but also easy to merge.

5. If there are high concurrency requirements or data table migration requirements, consider using the uniqueidentifier type and use the NEWID () function.

6. Consider establishing a unique index on the business primary key to achieve business requirements that are unique to the business primary key.

7, if you need to consider the performance requirements of the business primary key, you can set the business primary key to the clustered index, and the logical primary key only establishes primary key constraints and nonclustered indexes.

8, the relational table may consider the composite primary key method, the composite primary key is not used in the entity table.

Reference:

Relational database primary Key analysis
Hibernate development and combat
Thinking about the design of database primary key
[Discussion] Business primary key Vs. Logical primary key, which is good?
Logical primary KEY and joint primary key, must discuss clearly!
Questions about setting a primary key in a table
Questions about setting a primary key in a table

MSSQL-Logical primary key, business primary key, and composite primary key