Learn Programming

1631 readers

1 users here now

Posting Etiquette

Ask the main part of your question in the title. This should be concise but informative.
Provide everything up front. Don't make people fish for more details in the comments. Provide background information and examples.
Be present for follow up questions. Don't ask for help and run away. Stick around to answer questions and provide more details.
Ask about the problem you're trying to solve. Don't focus too much on debugging your exact solution, as you may be going down the wrong path. Include as much information as you can about what you ultimately are trying to achieve. See more on this here: https://xyproblem.info/

Icon base by Delapouite under CC BY 3.0 with modifications to add a gradient

founded 1 year ago

MODERATORS

[email protected]

Making a database identifier unique per user? (programming.dev)

submitted 3 months ago by [email protected] to c/[email protected]

18 comments fedilink hide all child comments

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?

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 6 points 3 months ago (2 children)

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.

[–] [email protected] 10 points 3 months ago (1 children)

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.

[–] [email protected] 6 points 3 months ago

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.

[–] [email protected] 1 points 3 months ago (1 children)

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.

[–] [email protected] 1 points 3 months ago

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.