Learn Programming

1625 readers

2 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 4 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] 10 points 4 months ago (2 children)

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

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

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)

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

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

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

You shouldn't use the name as a replacement for the ID, you need to use a slug.
The name should be stored as the user sets it, and the slug is autogenerated by your code removing any problematic character, so usually it only contains letters, numbers, and dashes, which makes it perfect to be a substitute for the numeric ID.
There should be libraries to handle this for you.

And ID is just something to identify a resource, so your ID in this case would be the slug.
I have a use case where the ID is generated by two fields, adapting it to your case would be something like /users/{user}/categories/{category}
So, whatever you define to be a unique way of working with an entity will be the identifier (ID) of that entity.

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

Good point, that sounds nicer than just encoding the name for sure, thanks

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

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.

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

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