Discussion on whether logical primary keys or business primary keys should be used in database tables

Yesterday, when he was dealing with the database of the online forum, he found that he used the post number as the primary key. Due to some accidental modifications to it, the association of the post became messy. So we discussed how to select the primary key in the database table. I am not familiar with the program of the forum, so I don't know how it is designed and implemented. Today, Ling Hu can discuss this topic on javaeye.

At first, I thought it would be a good way to use a meaningless logical primary key. At least one field can be used to determine only one record. It would be convenient to use and the speed should be faster. But after reading the discussion in the javaeye post and the discussion in the QQ group, I found that this is not exactly the case.

In fact, this is two different design ideas, not to mention the use of logical primary keys must be better than the use of business primary keys.

Using the business primary key is a traditional idea of C/S application development, including the sap I am using, and a large number of business primary keys are also used. However, if o/R Mapping is used, the logical primary key may be better.

Because
For traditional C/S applications, the frontend processes a data representation in a typical two-layer structure, while the backend processes a data persistence. Business logic is scattered at both ends, especially after
. Because the stored
Procedure, view, and so on implement business logic. applications directly deal with relational databases. Therefore, data records are not only required to facilitate program access, but also readable for developers. That is to say
The relational logic of databases can clearly express the business logic. It is natural or even necessary to use a business primary key for a primary key.

The opposite is true for ORM applications. It requires the simplest
Method to mark a unique record, but it does not need to be specific. Just like in OOP, when we access an object, we always use pointers (or similar references ), but we don't need to know this pointer
Whether the object value is 0x89abcdef or 0xfedcba98. A logical primary key is equivalent to a pointer. When another associated table references this record, a foreign key field is used to record this logical primary key,
This is equivalent to a property in the object that records a pointer to the object. In this case, if you use the business primary key, especially the composite business primary key, you will be in trouble. Most
The worst case is that when you need to modify the value of this business primary key, all the associations will be disordered-in traditional C/S applications, we use triggers to solve this problem, however
It is impossible for ORM to do this. Otherwise, what else will ORM do?

Of course, for developers, in the case of ORM, using logical primary keys has a crucial problem.
It is because the data readability is getting worse. That is to say, it is easy to understand data unless we look at it from the OO perspective. However, it is difficult to directly access the backend to view the relational database. Therefore, basically, the logic master
Keys and ORM complement each other and are indispensable. Developers Using ORM should try to avoid dealing with backend relational data. Otherwise, it will be very painful.

As Ling Hu summarized: one is from the OO perspective, and the other is to directly look into the database.