Generating sequential Ids for each user in the same Mysql table

Question

I have always used the primary key itself as the default identification code for each record in a database table. Once the table has been used by several users, and 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 primary key sequence. 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 of the following possibilities:

• When recording each user’s product, check its generated code (starting with 1, obviously) and then store in a user’s parameter field the next code to be used by it.

• Or, when engraving the product, fetch the code of the last product recorded in the table, and then increment + 1 for the new product. (I don’t think a proper way, because by deleting products, it could reuse erased code).

What they recommend to me?

Answer 1

Would you like to create a sequence for each user? I believe you could make the first option. create a table helps with ID_USER and its Code Code and at the time of giving the Insert you search what was the code Code and incrimenta +1 for the code!

The option of excluding a product would have no problem with the auxiliary table, and it would be nice for you to know how many products it has in total (excluded or not)

Answer 2

Boy, I think the first option better as the friend @Nicolas Pereira said before, I have already made an application with your second option but it was at the request of the client, precisely not to ask the generated sequence.