I have always used the primary key itself as the default ID for each record in a database table. Once the table has been used by several users, each one has its own registered products, it will no longer be possible to maintain that standard sequence (one by one) for each user, since the code maintains the sequence of the primary key. In this way, I would like to check a more optimized and practical way to generate sequential code for each user. When creating a new column for each user's sequential code, I thought about the following possibilities:
-
When saving the product of each user, check your generated code (starting with 1, obviously) and then store in a user parameter field the next code to be used by it.
-
Or, when recording the product, look for the code of the last product recorded in the table, and then increment + 1 for the new product. (I do not find a proper way because by deleting products, it could reuse deleted code).
What do you recommend?