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?
I have a join table between Category and other entities that can be categorized in this way, but I dont think I need one between User and Category? Different users can’t share the same category so it’s a 1-n relationship, not n-n.
Even if I did though I still have the same issue since I have to figure out how to autoincrement it, only now in the join table rather than the Category table.
if cats are unique to a person, you would just use your auto-created id for the category table. sounds like you need to separate your internal IDs from your external (human readable IDs)…
if you need something human-readable, you would concat an additional field in the category table with a ‘category display id’ or somethin