Key "nature" or "auto_increment" as the primary key

Question

My problem is that nature key and auto_increment integer as primary key.

For example, I have tables A and B and A_B_relation . A and B can be some objects, and A_B_realtion record the relationship of many, many of A and B.

Both A and B have their own global unique identifier, such as a UUID. The UUID is available to the user, which means that the user can request A or B by UUID.

There are two ways to create a primary key for a table.

• use the integer auto_increment. A_B_relation specify the integer as FK.
• use UUID. A_B_relation refers to the UUID as FK.

For example, a user wants to request all associated data B from A to UUID.

In the first case, the request flow is as follows:

 First, query A integer primary key by UUID from `A`. And then, query all the B integer primary key from `A_B_relation`. At last, query all the B info from `B`. 

In the latter case, the stream is as follows:

 Query all the B UUID from the `A_B_relation` by A UUID. Query all the B info from `B`. 

So, I think the latter case is more convenient. It is right? what is the disadvantage of the latter case?

Answer 1

In my opinion, the usability of either a natural auto-increment key key depends on the software solution that you provide. Both methods have pros and cons. Therefore, the best solution is to understand both types of keys, analyze which business solution you are trying to provide, and select the appropriate type of primary key.

A natural key is a column or set of columns that we can use to uniquely identify a record in a table. These columns contain real data that are relevant to the rest of the columns in the table. An auto-increment key , also called a surrogate key , is the only column in the table that contains unique numeric values ​​that can be used to uniquely identify a single row of data in the table. These values ​​are generated at run time when the record is inserted into the table and is not related to the rest of the row data.

The main advantage of using Natural keys is that it has its own meaning and requires less joining with other tables, where, as if we used a surrogate key, we would need to join the foreign key table to get the results obtained using the natural key.
But say that we cannot get all the data required from a separate table and join another table to get all the necessary data. Then, instead of the natural key, it’s convenient to use a surrogate key, because in most cases the natural keys are strings and larger than the surrogate keys, and it will take more time to join the tables using large values.

The natural key has its own meaning. Therefore, when it comes to finding records, it is more beneficial to use natural keys over surrogate keys. But over time, change our program logic, and we must change the value of the natural key. This will be complicated and cause a cascading effect in all respects of foreign keys. We can overcome this problem with a surrogate key. Since the surrogate key is not related to the rest of the string values, changes in the logic will not affect the surrogate key.

Likewise, as I see the convenience and inconvenience of using a surrogate key or natural key, completely based on the solution you provide.