Learn Programming @programming.dev Cyno @programming.dev 4mo ago

Making a database identifier unique per user?

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?

18 comments

i think youre missing a table.

you have categories, you have users, and then you have an 'assignment' table that contains a user key and a category key... maybe its own auto incrementing identifier
- Plus I'd suggest having a slug so the user doesn't have to memorize a meaningless number, instead a similar sounding string.
  
  Instead of having 12345, something like category-1 for "Category 1".
  Specially for sharing with a URL, it's more meaningful to share " domain.tld/search/categories/cat-1" than any other form of id (I'm annoyed with lemmy for not having a slug for posts, it feels so shady to share anything haha)
  
  This is something I've been considering too, since the name is in this case unique per user I can just use it for everything in frontend rather than the ID. It's not always a good solution though so I was wondering how would I solve it with IDs alone
- 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
You could also just use a random non-numeric primary key. For example you could generate a string of 8 random characters + numbers. That would give you well over 2 billion possible IDs.
- This is the kind of stuff that UUID’s are good for. When you want to generate non conflicting unique id’s. Even across systems and over time.
  
  I personally feel UUIDs are overused unless you happen to be running truly distributed systems that are all independently generating IDs.
  
  In this case where the ID is also going to be in the URL, you've just added 32 characters to the URL that don't need to be there. Since OP is apparently concerned with the look and feel of the URLs, I thought that UUIDs wouldn't be the best option.
- In the context of this small app im writing category is unique by name already so I can just use that if I wanted to go the string route, but agreed - yours is probably the standard way, youtube/reddit do it like that after all.
  
  I'm still wondering about the technical implementation of it - where would you generate the string? Manually in backend before each save, probably using a locking mechanism to prevent accidentally creating 2 identical IDs at the same time? I'd have to do a db hit to make sure it doesn't exist already every time, right? Maybe I just try to insert and see if it crashes due to the uniqueness index? Maybe I use a store procedure in the database to get a unique ID? Do I just hash the timestamp or sth like that?
  
  Whether I generate a number or a string, feels like I always open it up to many issues.
  
  For small apps, generating it in the backend, trying to insert it, and then catching the exception should be totally fine. The odds of collision are quite small.
Your description of the problem makes sense. If you think improving the user experience in this way is worthwhile, then go for it. Just remember, most users don't pay attention to the address bar.

For implementation, I suggest creating a new table, something like 'tb_user_category_index,' with userId as a foreign key and an integer that increments each time it's used. Automating this process in the database would be ideal: set it up once and let it run on its own, keeping things simple.

However, don't confuse this cosmetic index with a primary key. The primary key should only serve as a unique identifier for a record and hold no other information.
- By automating it you mean something a store procedure that returns the ID and increments the count at the same time or is there a more sophisticated way of doing it?
I think you shouldn't bother. First, as another user already said, the normal user does not look much at the address bar. Second, how will you work with deletions? Say user created 4 categories 1-4 and deletes category 2. Now you either have a hole or you "reindex" the categories to 1-3, which is probably bad since it breaks any bookmarks the user created.
- Deletions would work the same way as with a regular autoincrementing ID, it just always goes up. All it matters is that it doesn't expose how many other IDs are in the DB
User(Id, name etc) 1 — * UserCategory(UserId, CategoryId) * — 1 Category(Id, name etc)
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.

18 comments