Let’s say I am making an app that has table Category and table User. Each user has their own set of categories they created for themselves. Category has its own Id identity that is auto-incremented in an sqlite db.
Now I was thinking, since this is the ID that users will be seeing in their url when editing a category for example, shouldn’t it be an ID specific only to them? If the user makes 5 categories they should see IDs from 1 to 5, not start with 14223 or whichever was the next internal ID in the database. After all when querying the data I will only be showing them their own categories so I will always be filtering on UserId anyway.
So let’s say I add a new column called “UserSpecificCategoryId” or something like that - how do I make sure it is autogenerated in a safe way and stays unique per user? Do I have to do it manually in the code (which sounds annoying), use some sort of db trigger (we hate triggers, right?) or is this something I shouldn’t even be bothering with in the first place?
Ultimately, I don’t see any need for users to care about ids. I’d just make a user ID column in the category table and use that for selecting categories. Rather think about a display name that is either known or made by the user.
The only problem with increasing numbers is if you don’t properly limit access to creator account and a user can just edit the url bar to get others categories. It’s still a problem with other unique stuff, but less easy to crack when it’s not sequential.
If you really want it, just replace ID in the url with the category name, if that is a simple string without any special characters. Actually useful for users visually, avoids numbers, users only see what they care about, still no bridging table. And the query is still a simple where user_id = x and category_name = y. This actually means non-unique names, but you are always filtering on users, so you can instead use a constraint or unique composite index on user_id and category name.
Personally i would stick with IDs because they are simple, and don’t change so they are not prone to renaming, special character issues, and whatever else string handing deals with. And it’s probably slower.