[livibetter]

Here is my explanation:

The question is should we reuse deleted ID?

1. Says we have ID from 1 to 10. ID 5 is just deleted and a new user just registered. How does the program know which ID should be assigned to this new user? If this program reuse ID, then it have to go through the entire data, just for find out which ID is available. Or add another table for storing those deleted IDs? Reusing ID makes performance issue.

2. If this new users is assigned ID 5, then the admin suddenly find out he deleted wrong user, that should be another user. And if this program can partially restore data, can this program just do that? It can’t. ID 5 has been assigned to a new user, if you want to restore that deleted user, you may need to assign a new ID to that deleted user.

3. Now, think about a products database. You deleted a product, and inserted another one with same ID (assuming 5). If there is another program can order products using IDs only, it says it wants ID 5. But it actually meant old ID 5, not new ID 5. If you don’t reuse ID, then the transaction can’t be done.